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

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`;