某网站包含两个表,Customers
表和 Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers
表:
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders
表:
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格,你的查询应返回:
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
方法1:左外连接
SELECT C.Name AS Customers
FROM Customers AS C LEFT JOIN Orders AS O
ON C.ID=O.CustomerId
WHERE O.CustomerId is NULL
方法2:排除法
SELECT Name AS Customers
FROM Customers
WHERE Id NOT IN(SELECT CustomerId
FROM Orders)
方法3:排除法,使用EXISTS,语法:
EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或者逻辑假值“false”。
使用存在量词EXISTS后,若内层查询结果为非空,则外层的WHERE子句返回值为真,否则返回值为假。
与EXISTS谓词相对的是NOT EXISTS谓词。使用存在量词NOT EXISTS后,若对应查询结果为空,则外层的WHERE子语句返回值为真值,否则返回假值。
SELECT Name AS Customers
FROM Customers AS C
WHERE NOT EXISTS(SELECT * FROM Orders AS O
WHERE C.ID=O.CustomerId)