Find values that fail sql join on

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 re开发者_如何学JAVAcords 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?

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.

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)

try this:

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

SELECT *
FROM Table1 AS A
LEFT OUTER JOIN Table2 AS B
ON (A.Col = B.Col)
WHERE B.Col IS NULL