How to find non-existing data from another Table by JOIN?

mysql sql join anti-join

Blair Yumi · Sep 21, 2011 · Viewed 12.5k times · Source

I have two tables TABLE1 which looks like:

id      name     address
1       mm     123
2       nn     143

and TABLE2 w/c looks like:

name     age
mm      6
oo      9

I want to get the non existing names by comparing the TABLE1 with the TABLE2.

So basically, I have to get the 2nd row, w/c has a NN name that doesn't exist in the TABLE2, the output should look like this:

id      name     address
2      nn      143

I've tried this but it doesn't work:

SELECt  w.* FROM TABLE1 W INNER JOIN TABLE2 V
  ON W.NAME <> V.NAME

and it's still getting the existing records.

Answer

An INNER JOIN doesn't help here.

One way to solve this is by using a LEFT JOIN:

SELECT w.* 
FROM TABLE1 W 
LEFT JOIN TABLE2 V ON W.name = V.name
WHERE ISNULL(V.name);