difference between 2 tables

darkhalf · October 12th, 2006, 09:48 AM

Hi,

Have 2 different tables(table A, table B) with a list of users. Now I would like to list all the users in table A that are NOT in table B. How would one make the general SQL query for that?

Thank you,

jrogers · October 12th, 2006, 09:56 AM

select table1.user from table1 left join table2 on table1.user = table2.user where table2.user is null

Jeff Mason · October 12th, 2006, 09:57 AM

There are two ways.

First, you can use a subquery to find all the users in the first table NOT IN the second, as:

Code:

SELECT User FROM TableA WHERE User NOT IN (SELECT User FROM TableB)

or, being trickier, you can use an OUTER JOIN of the two tables and select the User from the TableB columns of the result where the User is NULL:

Code:

SELECT TableA.User
  FROM TableA
  LEFT OUTER JOIN TableB ON TableA.User = TableB.User
 WHERE TableB.User IS NULL

admittedly, the second query is harder to understand.

It's not clear which is more efficient. Indeed, they may result in the same execution plan. Or not.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com