[Teach Youself SQL in 10 Minutes] joining tables
一、inner joins
A?join based on the testing of equality between two tables is called equijoin. This kind of join is also called an inner join.
SELECT vend_name, prod_name, prod_priceFROM Vendors, ProductsWHERE Vendors.vend_id = Products.vend_id;
?
?
等价于:
?
SELECT vend_name, prod_name, prod_priceFROM Vendors INNER JOIN ProductsON Vendors.vend_id = Products.vend_id;
?
Per the ANSI SQL specification, use of the INNER JOIN syntax is preferable.
?
二、self joins
?
SELECT cust_id, cust_name, cust_contactFROM CustomersWHERE cust_name = (SELECT cust_name FROM Customers WHERE cust_contact = 'Jim Jones');
?
等价于:
?
SELECT c1.cust_id, c1.cust_name, c1.cust_contactFROM Customers AS c1, Customers AS c2WHERE c1.cust_name = c2.cust_nameAND c2.cust_contact = 'Jim Jones';
【SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_priceFROM Customers AS C, Orders AS O, OrderItems AS OIWHERE C.cust_id = O.cust_idAND OI.order_num = O.order_numAND prod_id = 'RGAN01';
?
The truth is, every inner join you have created thus far is actually a natural join, and you will probably never even need an inner join that is not a natural join.
?
四、Outer Joins
The join includes table rows that have no associated rows in the related table. This type of join is called an outer join. Such as:
SELECT Customers.cust_id, Orders.order_numFROM Customers LEFT OUTER JOIN OrdersON Customers.cust_id = Orders.cust_id;
?
?
?
2.RIGHT OUTER JOIN
?
SELECT Customers.cust_id, Orders.order_numFROM Customers RIGHT OUTER JOIN OrdersON Orders.cust_id = Customers.cust_id;
3. SELECT Customers.cust_id, Orders.order_numFROM Orders FULL OUTER JOIN CustomersON Orders.cust_id = Customers.cust_id;
?
?
?
NOTE:FULL OUTER JOIN Support The FULL OUTER JOIN syntax is not supported by Access, MySQL, SQL Server, or Sybase.
?
?
