查找不匹配的行
当我们想要查找左表中与右表中的行不匹配的行时,LEFT JOIN子句非常有用,可以在SELELCT WHERE语句中添加一个子句,以仅查询右表中列值包含NULL值的行。
以下是示例用到的两个表:
- orders表中的每个订单都必须属于customers表中的客户。
- customers表中的每个客户可以在表中包含零个或多个订单orders。
+------------------------+
| customers |
+------------------------+
| customerNumber |
| customerName |
| contactLastName |
| contactFirstName |
| phone |
| addressLine1 |
| addressLine2 |
| city |
| state |
| postalCode |
| country |
| salesRepEmployeeNumber |
| creditLimit |
+------------------------+
+----------------+
| orders |
+----------------+
| orderNumber |
| orderDate |
| requiredDate |
| shippedDate |
| status |
| comments |
| customerNumber |
+----------------+
如要查找尚未下订单的所有客户,可使用以下查询:
SELECT
c.customerNumber,
c.customerName,
orderNumber,
o.status
FROM
customers c
LEFT JOIN
orders o ON c.customerNumber = o.customerNumber
WHERE
orderNumber IS NULL;
+----------------+--------------------------------+-------------+--------+
| customerNumber | customerName | orderNumber | status |
+----------------+--------------------------------+-------------+--------+
| 125 | Havel & Zbyszek Co | NULL | NULL |
| 168 | American Souvenirs Inc | NULL | NULL |
| 169 | Porto Imports Co. | NULL | NULL |
| 206 | Asian Shopping Network, Co | NULL | NULL |
| 223 | Natrlich Autos | NULL | NULL |
| 237 | ANG Resellers | NULL | NULL |
| 247 | Messner Shopping Network | NULL | NULL |
...
WHERE子句与ON子句中的条件
使用LEFT JOIN子句来查询orders和orderDetails表中的数据,查询返回订单号10123及其详细信息(如果有)。
+-----------------+
| orderdetails |
+-----------------+
| orderNumber |
| productCode |
| quantityOrdered |
| priceEach |
| orderLineNumber |
+-----------------+
SELECT
o.orderNumber,
customerNumber,
productCode
FROM
orders o
LEFT JOIN
orderDetails USING (orderNumber) #这里相当于 ON o.oederNumber = orderDetails.orderNumber
WHERE
orderNumber = 10123;
+-------------+----------------+-------------+
| orderNumber | customerNumber | productCode |
+-------------+----------------+-------------+
| 10123 | 103 | S18_1589 |
| 10123 | 103 | S18_2870 |
| 10123 | 103 | S18_3685 |
| 10123 | 103 | S24_1628 |
+-------------+----------------+-------------+
但是,如果将条件从WHERE子句移动到ON子句:
SELECT
o.orderNumber,
customerNumber,
productCode
FROM
orders o
LEFT JOIN
orderDetails d ON o.orderNumber = d.orderNumber
AND o.orderNumber = 10123;
它会有不同的含义。
在这种情况下,查询将返回所有订单,但只有订单10123将具有与之关联的详细信息,如下所示。
运行结果:
+-------------+----------------+-------------+
| orderNumber | customerNumber | productCode |
+-------------+----------------+-------------+
| 10123 | 103 | S18_1589 |
| 10123 | 103 | S18_2870 |
| 10123 | 103 | S18_3685 |
| 10123 | 103 | S24_1628 |
| 10298 | 103 | NULL |
| 10345 | 103 | NULL |
| 10124 | 112 | NULL |
| 10278 | 112 | NULL |
| 10346 | 112 | NULL |
...
注意:INNER JOIN子句中的ON子句条件等同于WHERE子句中的条件。