SQL在多列上保留外连接(SQL left outer join on multiple columns)

SQL在多列上保留外连接(SQL left outer join on multiple columns)

根据此SQL连接备忘单 ，一列上的左外连接如下：

SELECT * FROM a LEFT JOIN b ON a.foo = b.foo WHERE b.foo IS NULL

我想知道它在多列上的连接会是什么样子，它应该是WHERE子句中的OR还是AND ？

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

要么

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

？

（我不认为它确实如此，但如果它很重要，数据库引擎是Vertica的）

（我打赌OR一个）

According to this SQL join cheat-sheet, a left outer join on one column is the following :

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 OR or an AND in the WHERE clause ?

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)

(I'm betting on the OR one)

最满意答案

这取决于列是否为空，但假设它们不是，检查它们中的任何一个都可以：

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

这是因为在成功连接之后，所有三列都将具有非空值。

如果这些列中的某些列可以为空并且您想要检查它们中的任何一个是否在连接后具有值，那么您的第一个（ OR ）方法就可以了。

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.