In SQL, how can I perform a subtraction operation?

Question

Suppose I have two tables, each of which has user IDs. I want to perform an operation that will return all user IDSs in table 1 that are not listed in table 2. I know there must be some easy way to do this - can anyone help?

Answer 1

Its slow, but you can usually do it with something like "not in." (There are other functions in various RDBMS systems to make this better; Oracle, for example, has an “exists” condition that can be used to do this.

But you could say:

select id from table1 where id not in (select id from table2) 

Answer 2

There are several ways to do this. Here is one approach using NOT EXISTS :

 SELECT userid FROM table1 WHERE NOT EXISTS ( SELECT * FROM table2 WHERE table1.userid = table2.userid ) 

And here is another approach using a join:

 SELECT table1.userid FROM table1 LEFT JOIN table2 ON table1.userid = table2.userid WHERE table2.userid IS NULL 

The fastest approach is database dependent.

Answer 3

One way is to use EXCEPT if your TSQL dialect supports it. This is equivalent to doing left and null test

Answer 4

 SELECT user_id FROM table1 LEFT JOIN table2 ON table1.user_id = table2.user_id WHERE table2.user_id IS NULL; 

Answer 5

If it is SQL Server:

 SELECT id FROM table1 EXCEPT SELECT id FROM table2 

Oracle:

 SELECT id FROM table1 MINUS SELECT id FROM table2 

Rest: not sure ....

Answer 6

Try the following:

 SELECT id FROM table1 WHERE id NOT IN ( SELECT id FROM table2 ) 

Answer 7

 select ID from table1 where ID not in (select ID from table2)