SQL WHERE NOT EXISTS 多个条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9819216/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
WHERE NOT EXISTS multiple conditions
提问by Gerry
How Do I set multiple AND conditions? ex.
如何设置多个 AND 条件？前任。
SELECT *
FROM CONFIRMED
WHERE NOT EXISTS
(
SELECT *
FROM Import_Orders
WHERE Import_Orders.Customer = CONFIRMED.Customer
AND Import_Orders.Reference = CONFIRMED.Reference
AND Import_Orders.[Index] = CONFIRMED.[Index]
AND Import_Orders.QuantityToDeliver = CONFIRMED.QuantityToDeliver
AND Import_Orders.DateToDeliver = CONFIRMED.DateToDeliver
);
I know this works on my tables with one WHERE & AND condition but not with several.
我知道这适用于我的表格，只有一个 WHERE & AND 条件，但不适用于几个。
I Need a result of two tables where the above conditions do not match. I do not have identical keys in the two tables. Now with this code I get all the results that are in table CONFIRMED.
我需要上述条件不匹配的两个表的结果。我在两个表中没有相同的键。现在通过这段代码，我得到了表 CONFIRMED 中的所有结果。
回答by Jirka Hanika
Here is the syntax for multiple tables:
以下是多个表的语法：
WHERE NOT EXISTS (...) AND NOT EXISTS (...) AND NOT EXISTS (...)
However, if the database is so large that you care about performance, you'll need a much less obvious syntax along the following lines:
但是，如果数据库太大以至于您关心性能，您将需要一个不太明显的语法，如下所示：
LEFT JOIN Some_Table t ON (t.xxx = Main_Table.xxx)
LEFT JOIN Another_Table t2 ON (t2.xxx = Main_Table.xxx)
LEFT JOIN Yet_Another_Table t3 ON (t3.xxx = Main_Table.xxx)
...
WHERE t.id IS NULL AND t2.id IS NULL AND t3.id IS NULL
For one table and one composed condition, like in the SQL sample in your question:
对于一张表和一个组合条件，如您问题中的 SQL 示例：
LEFT JOIN Some_Table t ON
t.xxx = Main_Table.xxx
AND t.yyy = Main_Table.yyy
AND t.zzz = Main_Table.zzz
WHERE t.id IS NULL
This is expected to return rows that exist in Main_Table but do not have matching rows in Some_Table, assuming the columns xxx, etc., are non-nullable.
这预计将返回 Main_Table 中存在但 Some_Table 中没有匹配行的行，假设 columnsxxx等是不可为空的。
If, for example, xxxis nullable, here is how you need to modify the query further:
例如，如果可以xxx为空，则需要进一步修改查询的方法如下：
LEFT JOIN Some_Table t ON
(t.xxx = Main_Table.xxx OR (t.xxx IS NULL AND Main_Table.xxx IS NULL))
AND t.yyy = Main_Table.yyy
AND t.zzz = Main_Table.zzz
WHERE t.id IS NULL
回答by Fionnuala
I am guessing that you have an ID on Import_Orders, if not use any field name that is turning up empty on the query. You would be better using field names rather than *. I have added an example for Import_Orders.
我猜你在 Import_Orders 上有一个 ID，如果没有使用任何在查询中变成空的字段名称。您最好使用字段名称而不是 *. 我为 Import_Orders 添加了一个示例。
SELECT CONFIRMED.*, Import_Orders.ID, Import_Orders.Customer
FROM CONFIRMED
LEFT JOIN Import_Orders
ON Import_Orders.Customer = CONFIRMED.Customer
AND Import_Orders.Reference = CONFIRMED.Reference
AND Import_Orders.[Index] = CONFIRMED.[Index]
AND Import_Orders.QuantityToDeliver = CONFIRMED.QuantityToDeliver
AND Import_Orders.DateToDeliver = CONFIRMED.DateToDeliver
WHERE Import_Orders.ID Is Null
More information
更多信息
Fundamental Microsoft Jet SQL for Access 2000Intermediate Microsoft Jet SQL for Access 2000Advanced Microsoft Jet SQL for Access 2000
Fundamental Microsoft Jet SQL for Access 2000Intermediate Microsoft Jet SQL for Access 2000Advanced Microsoft Jet SQL for Access 2000
回答by tmikulcek
You could just replace all the "=" with "<>" and you should get all the results that don't have a match on all criteria.
你可以用“<>”替换所有的“=”，你应该得到所有条件都没有匹配的结果。
SELECT *
FROM CONFIRMED
WHERE EXISTS
(
SELECT *
FROM Import_Orders
WHERE Import_Orders.Customer <> CONFIRMED.Customer
AND Import_Orders.Reference <> CONFIRMED.Reference
AND Import_Orders.[Index] <> CONFIRMED.[Index]
AND Import_Orders.QuantityToDeliver <> CONFIRMED.QuantityToDeliver
AND Import_Orders.DateToDeliver <> CONFIRMED.DateToDeliver
);
