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 ;

Hill

This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See How to Ask & the voting arrow mouseover texts. PS "left-join returns all records from the left table" That's poor, but typical, phrasing--because left join returns rows with the columns of both inputs. You are trying to say something involving left input rows being subrows of returned rows. – Messer

Answer 1

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.

Ignorance

Answer 2

Use Except to interpret

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

Prothrombin

MINUS isn't a keyword in SQL Server. Perhaps you're thinking of EXCEPT. – Countrywoman

Its an operator for reference techonthenet.com/sql/minus.php – Prothrombin

"MINUS isn't a keyword in SQL SERVER": db<>FIddle: Msg 102 Level 15 State 1 Line 3 Incorrect syntax near 'MINUS'. That is not to say it's isn't an operator in other SQL dialects, however, it is not an operator in T-SQL. – Countrywoman

Infact, that link you reference even confirms this: "TIP: The MINUS operator is not supported in all SQL databases. It can used in databases such as Oracle. For databases such as SQL Server, PostgreSQL, and SQLite, use the EXCEPT operator to perform this type of query." The OP has tagged SQL Server here, not Oracle. – Countrywoman

ohhh.. I thought its oracle. Thanks. – Prothrombin

No, the OP hasn't tagged Oracle at all here. – Countrywoman

My bad except would work then but the logic was same. Will change it to Except – Prothrombin

EXCEPT does not do exactly the same thing, because it removes duplicates. That might be desirable, but it isn't equivalent. – Ignorance

Answer 3

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

Kigali