SELECT * FROM table1 JOIN table2 ON table1.id = table2.id WHERE (table1.field1, table1.field2, table1.field3) != (table2.field1, table2.field2, table3.field3)
If the tables have no ID field in common, you might need a full outer join to find the differences between the tables. MySQL doesn't offer syntax for a full outer join, but you can implement one using the union of a left and a right join. Since no indexes are likely to be used, expect for these results to take a long time on tables of any significant size.
SELECT * FROM table1 LEFT JOIN table2 ON (table1.field1, table1.field2, table1.field3) = (table2.field1, table2.field2, table3.field3) WHERE table2.field1 IS NULL UNION SELECT * FROM table1 RIGHT JOIN table2 ON (table1.field1, table1.field2, table1.field3) = (table2.field1, table2.field2, table3.field3) WHERE table1.field1 IS NULL
Dig Deeper on Linux servers
Related Q&A from Scott Noyes
Get suggestions for choosing MySQL character sets and field collations from expert Scott Noyes. Continue Reading
A MySQL expert describes two ways that the multi-master support can be used. Continue Reading
An expert describes where to find information on detection deadlock algorithms in MySQL. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.