Find values that fail sql join on

Question

Say I have a query such as:

SELECT *
FROM Table_1
JOIN Table_2
ON Table_1.Col_1 = Table_2.Col_1

So I have 100 records and 98 of them are equal so that query would print out 98 out of 100. How can I get SQL to print the 2 that failed to meet the join?

Answer 1

Use a LEFT JOIN:

SELECT *
FROM Table_1
LEFT JOIN Table_2 ON (Table_1.Col_1 = Table_2.Col_1)

The fields of Table_2 will be NULL where there was no match for the ON clause. You'll then be able to add a WHERE TABLE_2.Col_1 IS NULL to keep only records in Table_1 that didn't have a match in Table_2.

Answer 2

An alternative to the LEFT JOIN is to use EXISTS.

SELECT * FROM Table_1
WHERE NOT EXISTS (SELECT * FROM Table_2 WHERE Col_1 = Table_1.Col_1)