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

FROM Vendors, Products

WHERE Vendors.vend_id = Products.vend_id;

 

 

等价于:

 

SELECT vend_name, prod_name, prod_price

FROM Vendors INNER JOIN Products

ON 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_contact

FROM Customers

WHERE cust_name = (SELECT cust_name

    FROM Customers

    WHERE cust_contact = 'Jim Jones');

 

等价于:

 

SELECT c1.cust_id, c1.cust_name, c1.cust_contact

FROM Customers AS c1, Customers AS c2

WHERE c1.cust_name = c2.cust_name

AND 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_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 = '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_num

FROM Customers LEFT OUTER JOIN Orders

ON Customers.cust_id = Orders.cust_id;

 

 

2.RIGHT OUTER JOIN

 

SELECT Customers.cust_id, Orders.order_num

FROM Customers RIGHT OUTER JOIN Orders

ON Orders.cust_id = Customers.cust_id;

3. FULL OUTER JOIN

 

SELECT Customers.cust_id, Orders.order_num

FROM Orders FULL OUTER JOIN Customers

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