MYSQL-7 Data Retrieval (5)

Junction (join)

One of the most powerful features of SQL is the ability to join tables in the execution of data retrieval queries. A junction is the most important operation that can be performed with a select of SQL.

Example: This example contains two tables, one table is vendors, the Vendors table contains all vendor information, each vendor has a single row, each vendor has a unique identity, which is called the primary key and can be a vendor ID or any other unique value.

The Products table stores only the product information, which does not store other vendor information other than the store vendor ID (the primary key of the vendors table), and the primary key of the vendors table is also called the foreign key of the goods, and it associates the Vendors table with Products table. The vendor ID can be used to find the appropriate vendor details from the Vnedors table.

SELECT * FROM Products;

SELECT * from vendors;

SELECT * from OrderItems;

Two table joins

SELECT Vend_name, Prod_name, prod_price from vendors, products WHERE vendors. ' vend_id ' = products. ' vend_id ' ORDER by ve Nd_name,prod_name;


SELECT Vend_name, Prod_name, prod_price from vendors INNER joins products on vendors. ' vend_id ' = products. ' vend_id ';

Multiple table joins

20005;

Using Table aliases

SELECT CONCAT (RTRIM (vend_name),'(', RTRIM (vend_country),')' as Vend_title from vendors ORDER by Vend_name;

Create an advanced junction, using table aliases

' TNT2 ';

Self-coupling

' dtntr ');   Self-query
SELECT P1.prod_id,p1.prod_name from the products as P1, the products as P2 WHERE p1. ' vend_id ' =p2. ' vend_id ' and P2. ' prod_id ' = ' dtnt R ';  Self-coupling

Natural coupling

' FB ';

External coupling

SELECT customers. ' cust_id ', orders. ' Order_num ' from customers to 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 joins 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 the products as P1, the 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 W Here 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 to OUTER JOIN orders on customers. ' cust_id '= orders. ' cust_id ';

MYSQL-7 Data Retrieval (5)