多列上的 SQL 左外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40015779/
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
SQL left outer join on multiple columns
提问by Fran?ois M.
According to this SQL join cheat-sheet, a left outer join on one column is the following :
根据此SQL 连接备忘单，一列上的左外连接如下：
SELECT *
FROM a
LEFT JOIN b
ON a.foo = b.foo
WHERE b.foo IS NULL
I'm wondering what it would look like with a join on multiple columns, should it be an ORor an ANDin the WHEREclause ?
我想知道在多个列上连接会是什么样子，它应该是子句中的anOR还是 an ？ANDWHERE
SELECT *
FROM a
LEFT JOIN b
ON a.foo = b.foo
AND a.bar = b.bar
AND a.ter = b.ter
WHERE b.foo IS NULL
OR b.bar IS NULL
OR b.ter IS NULL
or
或者
SELECT *
FROM a
LEFT JOIN b
ON a.foo = b.foo
AND a.bar = b.bar
AND a.ter = b.ter
WHERE b.foo IS NULL
AND b.bar IS NULL
AND b.ter IS NULL
?
?
(I don't think it does, but in case it matters, the db engine is Vertica's)
（我不认为它确实如此，但万一重要，数据库引擎是 Vertica 的）
(I'm betting on the ORone)
（我打赌OR一个）
回答by Cristian Lupascu
That depends on whether the columns are nullable, but assuming they are not, checking any of them will do:
这取决于列是否可以为空，但假设它们不是，检查它们中的任何一个都可以：
SELECT *
FROM a
LEFT JOIN b
ON a.foo = b.foo
AND a.bar = b.bar
AND a.ter = b.ter
WHERE b.foo IS NULL -- this could also be bar or ter
This is because after a successful join, all three columns will have a non-null value.
这是因为在成功加入后，所有三列都将具有非空值。
If some of these columns were nullable and you'd like to check if any one of them had a value after the join, then your first (OR) approach would be OK.
如果其中一些列可以为空，并且您想检查它们中的任何一个在连接后是否具有值，那么您的第一个 ( OR) 方法就可以了。
回答by Amir Rahimi Farahani
You can use any combination of criteria for joining:
您可以使用任何条件组合来加入：
SELECT *
FROM a
LEFT JOIN b ON a.foo = b.foo AND a.bar = b.bar AND a.ter = b.ter
The WHEREclause has nothing to do with the join itself.
The WHERE b.foo IS NULLin first query will return all records from athat had no matching records in bor when b.foowas null.
该WHERE子句与连接本身无关。第WHERE b.foo IS NULL一个查询将返回a没有匹配记录的所有记录 inb或 when b.foowas null。
