[Teach Youself SQL in 十 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';

【No AS in Oracle Oracle users, remember to drop the AS.】

?

三、Natural Joins

A natural join is a join in which you select only columns that are unique. This is typically done using a wildcard (SELECT *) for one table and explicit subsets of the columns for all other tables. The following is an example:

?

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:

????1) List all products with order quantities, including products not ordered by anyone;

??? 2)Calculate average sale sizes, taking into account customers who have not yet placed an order

?

When using OUTER JOIN syntax you must use the RIGHT or LEFT keywords to specify the table from which to include all rows (RIGHT for the one on the right of OUTER JOIN, and LEFT for the one on the left).

?

NOTE:It is important to note that the syntax used to create an outer join can vary slightly among different SQL implementations. The various forms of syntax described in the following section cover most implementations, but refer to your DBMS documentation to verify its syntax before proceeding.

1.LEFT OUTER JOIN

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. FULL OUTER JOIN

?

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.

?

?