Left-join EXCLUDING matching records?

Question

Traditional left-join returns all records from the left table, including matching records:

I want to use the join to exclude matching records, and return only non-matching records from the left table:

Shown below, is the code I came up with so far.
It uses a WHERE clause to weed out matching records - but this feels wrong somehow.
Is this the best way to do this? Or is there a better method of exclusive joining?

SELECT L.col1 ,
       L.col2 ,
FROM leftTable L
LEFT JOIN rightTable R ON R.col1 = L.col1
WHERE R.id IS NULL ;

user_1144035 · Answer

The LEFT JOIN method is fine. It is optimized in many databases. Personally, I prefer NOT EXISTS, because I think it is more concise:

SELECT L.col1, L.col2
FROM leftTable L
WHERE NOT EXISTS (SELECT 1 FROM rightTable R WHERE R.col1 = L.col1);

That is, the logic is in one place (the NOT EXISTS expression) rather than being spread over two query clauses.

user_10497483 · Answer

Use Except to interpret

   Select * from table t LEFT JOIN 
     TABLE1 t1
   On t.id=t1.id
   Except
 SELECT * FROM TABLE1

user_12129698 · Answer

There is nothing wrong in using left join approach in fact it is standard approach and most people follow this way to exclude records and incremental inserts in some cases

Left-join EXCLUDING matching records?

3 Answers