Mysql-7 data retrieval (5), mysql-7 Data Retrieval

Source: Internet
Author: User
Tags rtrim

Mysql-7 data retrieval (5), mysql-7 Data Retrieval
Join)

One of the most powerful functions of SQL is to join tables in the execution of data retrieval and query. Join is the most important operation that can be performed using SQL SELECT.

Example: This example contains two tables. One table is vendors. The vendors table contains information about all suppliers. Each supplier occupies one row and each supplier has a unique identifier. This identifier is called a primary key, it can be the supplier ID or any other unique value.

The products table only stores product information. In addition to the supplier ID (primary key of the vendors table), it does not store other supplier information. The primary key of the vendors table is also called the foreign key of products, it associates the vendors table with the products table, and uses the supplier ID to find detailed information about the corresponding supplier from the vnedors table.

SELECT * FROM products;

SELECT * FROM vendors;

SELECT * FROM orderitems;

Join two tables
SELECT vend_name , prod_name , prod_price FROM vendors , products WHERE vendors.`vend_id` = products.`vend_id` ORDER BY vend_name,prod_name;


SELECT vend_name , prod_name , prod_price FROM vendors INNER JOIN products ON vendors.`vend_id` = products.`vend_id`;

 

Join multiple tables
SELECT prod_name , vend_name ,prod_price , quantity FROM orderitems , products , vendors WHERE products.`vend_id` = vendors.`vend_id` AND orderitems.`prod_id` = products.`prod_id` AND order_num = 20005;

 

Use table alias
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;

 

Create an advanced join and use the table alias
SELECT cust_name , cust_contact FROM customers AS c , orders AS o ,orderitems AS oi WHERE c.`cust_id` = o.`cust_id` AND oi.`order_num` = o.`order_num` AND prod_id = 'TNT2';

Auto join
SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'dtntr '); self-Query
SELECT p1.prod _ id, p1.prod _ name FROM products AS p1, products AS p2 WHERE p1. 'vend _ id' = p2. 'vend _ id' AND p2. 'prod _ id' = 'dtntr'; Auto-join

Natural connection
SELECT c.* , o.order_num ,o.order_date, oi.prod_id, oi.quantity, OI.item_price FROM customers AS c ,orders AS o, orderitems AS oi WHERE c.`cust_id` =o.`cust_id` AND oi.`order_num` = o.`order_num` AND prod_id = 'FB';

 

External connections
SELECT customers.`cust_id`,orders.`order_num` FROM customers LEFT OUTER JOIN orders ON customers.`cust_id` = orders.`cust_id`;

  

 

 

SELECT vend_name , prod_name , prod_price FROM vendors , products WHERE vendors.`vend_id` = products.`vend_id` ORDER BY vend_name,prod_name;SELECT vend_name , prod_name , prod_price FROM vendors INNER JOIN products ON vendors.`vend_id` = products.`vend_id`;SELECT prod_name , vend_name ,prod_price , quantity FROM orderitems , products , vendors WHERE products.`vend_id` = vendors.`vend_id` AND orderitems.`prod_id` = products.`prod_id` AND order_num = 20005;SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;SELECT cust_name , cust_contact FROM customers AS c , orders AS o ,orderitems AS oi WHERE c.`cust_id` = o.`cust_id` AND oi.`order_num` = o.`order_num` AND prod_id = 'TNT2';SELECT prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');SELECT p1.prod_id,p1.prod_name FROM products AS p1, products AS p2 WHERE p1.`vend_id`=p2.`vend_id` AND p2.`prod_id`= 'DTNTR';SELECT c.* , o.order_num ,o.order_date, oi.prod_id, oi.quantity, OI.item_price FROM customers AS c ,orders AS o, orderitems AS oi WHERE c.`cust_id` =o.`cust_id` AND oi.`order_num` = o.`order_num` AND prod_id = 'FB';SELECT customers.`cust_id`,orders.`order_num` FROM customers LEFT OUTER JOIN orders ON customers.`cust_id` = orders.`cust_id`;

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.