SQL 之 外连接

关键字：LEFT OUTER JOIN , RIGHT OUTER JOIN , FULL JOIN

Customers表

CustomerID        FirstName        LastName

-------------------------------------------------------

        1                   William           Smith

        2                   Natalie           Lopez

        3                   Brenda           Harper

        4                   Adam            Petrie

Orders表

OrderID    CustomerID     OrderDate     OrderAmount

---------------------------------------------------------------------------

        1                   1           2009-09-01       10.00

        2                   2           2009-09-02       12.50

        3                   2           2009-10-03       18.00

        4                   3           2009-09-15       20.00

Refunds表

OrderID    CustomerID     OrderDate     OrderAmount

---------------------------------------------------------------------------

        1                   1           2009-09-02       5.00

        2                   3           2009-10-12       18.00

4位客户下了3笔订单，4笔订单中有2笔退货。

1. 左连接

SELECT

C.FirstName AS ‘First Name’,

C.LastName AS ‘Last Name’,

O.OrderDate AS ‘Order Date’,

O.OrderAmount AS ‘Order Amt’,

R.RefundDate AS ‘Refund Date’,

R.RefundAmount AS ‘Refund Amt’,

FROM Customer AS C                     # 第一个表是Customers表

LEFT JOIN Order AS O                    # 第二个表是Orders表，出现在第一个LEFT JOIN之后

ON C.CustomerID =O.CustomerID  # 这一个ON子句指定了如何把Orders表左连接到Customers表

LEFT JOIN Refunds AS R            # 第二个表是Refunds表，出现在第二个LEFT JOIN之后

ON O.OrderID = R.OrderID          # 第二个LEFT JOIN之后的ON子句指定了如何把Refunds表左连接到Orders表

ORDER BY C.CustomerID, O.OrderID, R.RefundID

LEFT左边的表总是主表，右边的是从表，我们需要主表的所有行，即使从表中没有任何行与之匹配（从表的这一列显示NULL）。

第一个连接：Customers表是主表，Orders表是从表；第二个连接，Orders表是主表，Refunds表是从表；

第一个连接后上面的结果是：所有客户的订单和退货数据（如果没有订单或者退货，对应列为NULL）
CustomerID为4的客户的行中，OrdersID等都是NULL，因为该客户没有订单。第二个连接，OrderID为2和4的订单的Refund Date和Refund Amt都是NULL，因为这些订单没有退货。

最后用ORDER BY子句排序，注意在SELECT的columnlist里面没有选择ORDER BY子句中指定的字段。

上面的结果是：所有客户的身份信息，订单和退货数据（如果没有订单或者退货，对应列为NULL）

可以看到有1位客户没有订单，有两笔订单没有退货。LEFT JOIN允许了这些没有值的行出现（INNER JOIN不同，内连接的两个表都是主表或者说是必需的表）。

怎样获得那些没有退货的订单？添加一条判断空值的WHERE语句：

SELECT

C.FirstName AS ‘First Name’,

C.LastName AS ‘Last Name’,

O.OrderDate AS ‘Order Date’,

O.OrderAmount AS ‘Order Amt’,

R.RefundDate AS ‘Refund Date’,

R.RefundAmount AS ‘Refund Amt’,

FROM Customer AS C                   

LEFT JOIN Order AS O                   

ON C.CustomerID =O.CustomerID  

LEFT JOIN Refunds AS R           

ON O.OrderID = R.OrderID       

WHERE O.OrderIDIS NOT NULL

ANDR.RefundIDIS NULL

ORDER BY C.CustomerID, O.OrderID, R.RefundID

2. 右连接

和左连接一样，只是RIGHT JOIN的右边是主表。

建议：有多个表的复杂FROM子句，建议使用LEFT JOIN并且避免使用圆括号。

3. 全连接

全连接的两个表都是从表（可选的表），如果表A和表B的行匹配，那么会显示：

（1）表A的所有行，即使它在B中没有匹配的行

（2）表B的所有行，即使它在A中没有匹配的行

与SQL和Oracle不同，MySQL不提供全连接（关联性不够强）。