compare differences between two tables in mysql

Question

Same as http://stackoverflow.com/questions/688537/ except in mysql.

Suppose I have two tables, t1 and t2 which are identical in layout but which may contain different data.

What's the best way to diff these two tables?

To be more precise, I'm trying to figure out a simple SQL query that tells me if data from one row in t1 is different from the data from the corresponding row in t2

It appears I cannot use the intersect nor minus. When I try

SELECT * FROM robot intersect SELECT * FROM tbd_robot

I get an error code:

[Error Code: 1064, SQL State: 42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM tbd_robot' at line 1

Am I doing something syntactically wrong? If not, is there another query I can use?

Edit: Also, I'm querying through a free version DbVisualizer. Not sure if that might be a factor.

Answer 1

You can construct the intersection manually using UNION. It's easy if you have some unique field in both tables, e.g. ID:

SELECT * FROM T1
WHERE ID NOT IN (SELECT ID FROM T2)

UNION

SELECT * FROM T2
WHERE ID NOT IN (SELECT ID FROM T1)

If you don't have a unique value, you can still expand the above code to check for all fields instead of just the ID, and use AND to connect them (e.g. ID NOT IN(...) AND OTHER_FIELD NOT IN(...) etc)

Answer 2

INTERSECT needs to be emulated in MySQL:

SELECT  'robot' AS set, r.*
FROM    robot r
WHERE   ROW(r.col1, r.col2, …) NOT IN
        (
        SELECT  *
        FROM    tbd_robot
        )
UNION ALL
SELECT  'tbd_robot' AS set, t.*
FROM    tbd_robot t
WHERE   ROW(t.col1, t.col2, …) NOT IN
        (
        SELECT  *
        FROM    robot
        )

Answer 3

how about 'minus' in mysql? is it needs to be emulated in MySQL also? any idea how to do it?