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 TableB
ON TableA.ID = TableB.ID
WHERE 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.