Two tables, each containing an integer "id" column. 

I want to get a list of ids that aren't contained in BOTH tables. For
example, if the tables looked like this:

1.id: 1,2,3,5,6,7,10,9
2.id: 1,2,3,4,5,6,7,8,9,10

The query should return: 4,8

The id column is the primary key in both tables.

How can I do this reasonably quickly? (i.e. less than a minute or so
with 100k+ rows in each.) The number of ids returned is likely to be
only a handful. It's ok if the search is "one-way"; i.e. only finds
ids in table 1 that don't exist in table 2.

---

Request for Question Clarification by mathtalk-ga on 10 Feb 2003 09:15 PST

Hi, alexander-ga:

It seems to me you found your own answer here, using an outer join and
checking for Null on the primary key of the optional side of the join.
 Your search may have been inspired by sysin-ga's comment suggesting a
subquery, but sysin-ga is not a researcher and cannot post your
solution as an answer.  [One way to tell is by the presence of a link
for your name in the Comments section and the absence of a link for
sysin-ga's name.  This is a relatively new feature.]

If you have another question you'd like to raise here as
"clarification", I'm sure your fellow researchers would be happy to
apply their skills.  Otherwise you might want to go ahead and expire
your question.  You are a tough act to follow!

regards, mathtalk-ga

---

Clarification of Question by alexander-ga on 11 Feb 2003 15:06 PST

Good catch, mathtalk. :)

sysin, thanks for the help.

Hi Alexander,
 That was a good Question!

 I tested your question creating 2 tables as follows
 creat table test_table1(ida,namea)
 creat table test_table2(idb,nameb)

 I then inserted the following rows 
 insert into test_table1(ida,namea) values(1,'geetha')
 insert into test_table1(ida,namea) values(2,'neetha')
 insert into test_table1(ida,namea) values(3,'seetha')
 insert into test_table2(idb,nameb) values(1,'abc')
 insert into test_table2(idb,nameb) values(4,'def')
 insert into test_table2(idb,nameb) values(5,'ghi')

 Heres the result on both the tables Select query
 select * from test_table1
 IDA     NameA
 ---     -----
 1       geetha
 2       neetha
 3       seetha

 select * from test_table2
 IDB     nameB
 ---     -----
 1       abc
 4       def
 5       ghi


 And as for your question a query on the 2 tables should return
 rows for the field ID present only in one table.
 So heres your Query!
 
 select idb from test_table2
 where idb not in
 (select ida from test_table1)

 And the result of the above Query
 IDA
 ---
 4
 5
 
 Hope that answers your question.
 Please let me know if you have any further questions on the query.

Thanks,
sysin.

Ah, but MySQL doesn't yet support subselects. (in 4.1 tree, still in
development, I'm using 4.0)

However, your solution quickly led me to this page on the MySQL site:

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

which suggests a different way of writing it:

SELECT test_table2.idb FROM test_table2 LEFT JOIN test_table1 ON
test_table2.idb=test_table1.ida WHERE test_table1.ida IS NULL;

This works perfectly, and only takes about 3 seconds on my tables with
about 180k rows.

You may post as an answer.