Simpler way to get a diff of 2 mySQL tables?

Question

I have two tables with identical fields, 35 identical fields.

I know I can find out what rows do not exist in one of them by using

SELECT first.a, first.b, first.c FROM first LEFT JOIN second USING(a,b,c) WHERE second.a IS NULL

What I am wondering is whether there is any simpler way to write this considering that the table columns and column order are identical?

Will · Accepted Answer · 2013-08-07 13:58:06Z

3

Another option is:

SELECT DISTINCT a, b, c FROM first WHERE (a, b, c) NOT IN (SELECT a, b, c FROM second)

It isn't a whole lot simpler, but it may be what you're looking for. ~~Also, your version should be more efficient.~~

answered Feb 16, 2011 at 17:16

9052 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Actually the NOT IN and the NOT EXISTS versions are usually more efficient in MySQL.

And the link from that question to a blog post about this comparison: explainextended.com/2009/09/15/…

Eric Petroelje · Accepted Answer · 2011-02-16 17:13:54Z

Unfortunately no, not in MySQL.

In most modern DBMS's you can use the MINUS operator for that:

SELECT col1, col2, col3 FROM tablea MINUS SELECT col1, col2, col3 FROM tableb

But MySQL does not support the MINUS operator yet.