select a value where it doesn't exist in another table
You could use NOT IN
:
SELECT A.* FROM A WHERE ID NOT IN(SELECT ID FROM B)
However, meanwhile i prefer NOT EXISTS
:
SELECT A.* FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.ID=A.ID)
There are other options as well, this article explains all advantages and disadvantages very well:
Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?
For your first question there are at least three common methods to choose from:
- NOT EXISTS
- NOT IN
- LEFT JOIN
The SQL looks like this:
SELECT * FROM TableA WHERE NOT EXISTS ( SELECT NULL FROM TableB WHERE TableB.ID = TableA.ID)SELECT * FROM TableA WHERE ID NOT IN ( SELECT ID FROM TableB)SELECT TableA.* FROM TableA LEFT JOIN TableBON TableA.ID = TableB.IDWHERE TableB.ID IS NULL
Depending on which database you are using, the performance of each can vary. For SQL Server (not nullable columns):
NOT EXISTS and NOT IN predicates are the best way to search for missing values, as long as both columns in question are NOT NULL.
This would select 4 in your case
SELECT ID FROM TableA WHERE ID NOT IN (SELECT ID FROM TableB)
This would delete them
DELETE FROM TableA WHERE ID NOT IN (SELECT ID FROM TableB)