SQL left outer join on multiple columns

Question

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)

Tendon

Answer 1

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.

Gantry

Answer 2

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 WHERE clause has nothing to do with the join itself. The WHERE b.foo IS NULL in first query will return all records from a that had no matching records in b or when b.foo was null.

Brinkema

Answer 3

If you want without LEFT JOIN key words but with (+) you can do like this:

SELECT *
    FROM a, b 
    WHERE a.foo = b.foo (+)
    AND a.bar = b.bar (+)
    AND a.ter = b.ter (+)
    AND b.foo IS NULL

Reader