In SQL, how can I perform a "subtraction" operation?
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)
There are a few ways to do it. Here's one approach using NOT EXISTS:
SELECT useridFROM table1WHERE NOT EXISTS( SELECT * FROM table2 WHERE table1.userid = table2.userid)And here's another approach using a join:
SELECT table1.useridFROM table1LEFT JOIN table2ON table1.userid = table2.useridWHERE table2.userid IS NULLThe fastest approach depends on the database.
One way is to use EXCEPT if your TSQL dialect supports it. It is equivalent to performing a left join and null test