根据这份SQL join cheat-sheet显示,一个基于一列的左外连接是如下所示的:
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
)