compare data in two tables and output the "different records"

blackhorse

I have two simple tables, tb1, tb1_old.

both tables have 3 fields: id1, id2, createdat.

tb1 will have most records in tb1_old but with some new records added and with some old recordes.

I want a query to compare these two tables, and generated the records that which are in tb1_old but not in tb1 (the deleted records), and the records which are in tb1 but not in tb1_old (the added new records).

My first thoughts are grab these records from tb1 and tb1_old and use array functions in php to get the difference of two sets of the records.

I am looking for the mysql query to do the same work? Any advices?

Left Join should do the work, right?

blackhorse

SELECT tb1.id FROM tb1 LEFT JOIN tb2 ON (tb1.id1=tb2.id1 AND tb1.id2=tb2.id2) WHERE tb2.id1 IS NULL

This will do the job.

But now if tb1 and tb2 both have a field called "description", and description could be null

Now if I run this left join

SELECT tb1.id FROM tb1 LEFT JOIN tb2 ON (tb1.id1=tb2.id1 AND tb1.id2=tb2.id2 AND tb1.description=tb2.description) WHERE tb2.id1 IS NULL

I would not just got the records in tb1 which has no identical record in tb1 (these are records I needed) but also I will get the records which has the "description" as null even it is identical in tb2. (these records I don't want). How do I fix this query to only return the records in tb1 that has no identical record in tb2?

Thanks!

johanafm

WHERE tb2.id1 IS NULL AND tb1.description IS NOT NULL

Personally, I find this query to be a lot easier to read

SELECT *
FROM tb1
WHERE (id, id2, description) NOT IN (SELECT id, id2, description FROM tb2)

Also, are you aware that you do NOT get all the record that exist in one table but not the other, but rather only those that exist in tb1 but not in tb2?

Bjom

SELECT id1, id2, description
FROM tb1
WHERE (id1, id2, description) NOT IN (SELECT id1, id2, description FROM tb2)
UNION SELECT id1, id2, description
FROM tb2
WHERE (id1, id2, description) NOT IN (SELECT id1, id2, description FROM tb1)

this should get them all

blackhorse

thanks!

Roger_Ramjet

Get the records from 1 table that are NOT in another table is best done with a LEFT JOIN and IS NULL

SELECT * FROM tb1 LEFT JOIN tb1_old USING(id1) WHERE tb1_old.id1 IS NULL

You reverse this for the records that are in tb1_old but not in tb1. You can then use Bjom's UNION to get them all in 1 result set.

PS the reason that this is best done with a join is because the db engine can do it all using indexes. The other method ends up involving a table scan and that is less efficient.

blackhorse

But I will need to compare (use) several fields, not just only using the id field. Plus there will be null values fields and I have to use nullif to compare that if these fields are "=" in both tables.

Here is my query

SELECT tb1.my_id FROM tb1 LEFT JOIN tb1_old ON ((tb1_old.active=1) AND (tb1.my_id = tb1_old.my_id) AND (tb1.name = tb1_old.name) AND (NULLIF (tb1.description, tb1_old.description) IS NULL AND NULLIF (tb1_old.description, tb1.description) IS NULL) AND (NULLIF (tb1.location, tb1_old.location) IS NULL AND NULLIF (tb1_old.location, tb1.location) IS NULL) AND (NULLIF (tb1.website, tb1_old.website) IS NULL AND NULLIF (tb1_old.website, tb1.website) IS NULL) AND (NULLIF (tb1.capacity, tb1_old.capacity) IS NULL AND NULLIF (tb1_old.capacity, tb1.capacity) IS NULL) AND (NULLIF (tb1.image, tb1_old.image) IS NULL AND NULLIF (tb1_old.image, tb1.image) IS NULL)) WHERE tb1_old.my_id IS NULL

That is for the simplest table compare. There will be other tables with more fields to compare, although the records are not very many.

In this case, should it be better to use johanafm's suggestion of using MySQL's row subquery, like

SELECT column1,column2,column3
       FROM t1
       WHERE (column1,column2,column3) IN
             (SELECT column1,column2,column3 FROM t2);

Or still using the left join as I did before.

Thanks!

johanafm

According to Roger you should stick with your query for speed. Run both versions through EXPLAIN if you want to see the difference.