[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.

?

?