Compare two table in mysql

Try this:

SELECT table1.*
FROM table1
LEFT OUTER JOIN table2
ON table1.id = table2.id
WHERE table2.id IS NULL

LEFT OUTER JOIN link two table starting by table1, if table2 has no linked row all fields of table2 will be null. So, if you put in your WHERE condition table2.id is null, you get only rows in table1 not existing in table2


You could solve this by doing a left outer join and checking for all rows that don't exist. Try the following depending on if you want to find values not existent from table1 in table2 or table2 in table1.

SELECT *
FROM table1
LEFT OUTER JOIN table2 ON (table1.id = table2.id)
WHERE table2.id IS NULL;


SELECT *
FROM table2
LEFT OUTER JOIN table1 ON (table1.id = table2.id)
WHERE table2.id IS NULL;

SQL Fiddle: http://sqlfiddle.com/#!2/a9390/8

Tags:

Mysql

Compare