Simpler way to get a diff of 2 mySQL tables?

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

开发者_StackOverflow社区

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?

---

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.

---

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.