2

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?

jerrygarciuh
  • 21,158
  • 26
  • 82
  • 139

2 Answers2

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.

Will
  • 875
  • 2
  • 8
  • 19
  • Actually the `NOT IN` and the `NOT EXISTS` versions are usually more efficient in MySQL. – ypercubeᵀᴹ Feb 16 '11 at 17:55
  • Check this: http://stackoverflow.com/questions/3604042/the-old-in-vs-exists-vs-left-join-where-is-or-is-not-null-performance – ypercubeᵀᴹ Feb 16 '11 at 19:33
  • And the link from that question to a blog post about this comparison: http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ – ypercubeᵀᴹ Feb 16 '11 at 19:34
2

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.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177