In SQL, how can I perform a "subtraction" operation?

Question

Suppose I have two tables, which both have user ids. I want to perform an operation that would return all user IDS in table 1 that are not in table 2. I know there has to be some easy way to do this - can anyone offer some assistance?

Inspection

What database platform are you using? – Lamonicalamont

Answer 1

Its slow, but you can normally accomplish this with something like 'not in'. (There are other functions in various RDBMS systems to do this in better ways, Oracle for instance has a 'exists' clause that can be used for this.

But you could say:

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

Orfield

Answer 2

There are a few ways to do it. Here's one approach using NOT EXISTS:

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

And here's 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 depends on the database.

Wrench

Answer 3

One way is to use EXCEPT if your TSQL dialect supports it. It is equivalent to performing a left join and null test

Deyo

Answer 4

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

Spiro

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: Am not sure....

Tuberous

Answer 6

Try this:

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

Forint

Answer 7

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

Fu