mysql - 使用 EXCEPT 子句时出错

Question

上面附上的是我想解决的问题。首先，我想尝试在 SQL 中使用 EXCEPT 子句，并获取 Orders 表中不存在的所有值。这就是我正在尝试的:(暂时忽略列名和其他内容，我只想测试 EXCEPT 逻辑)

SELECT Id, Name FROM Customers
EXCEPT
SELECT O.CustomerId as Id, C.Name AS Name FROM Customers C , Orders O
WHERE C.Id = O.CustomerId

但是，我在 LeetCode 上遇到了这个错误，我不确定:

Line 3: SyntaxError: near 'SELECT O.CustomerID as Id, C.Name AS Name FROM Customers C , Orders O WHERE C.Id'

我希望从我编写的查询中得到的预期输出是:

Id    Name
2     Henry
4     Max

编辑:这是LeetCode上的问题，查看它使用的版本后我发现它是mysql-server 5.7.21

Answer 1

如果您坚持使用EXCEPT，请设为EXCEPT ALL:

SELECT Id FROM Customers
EXCEPT ALL
SELECT CustomerID FROM Orders;

但我宁愿使用LEFT JOIN/.. IS NULL:

SELECT C.*
FROM   Customers C
LEFT   JOIN Orders O ON O.CustomerId = C.Id
WHERE  O.CustomerId IS NULL;

参见:

• Select rows which are not present in other table