How to check when a field value from one table does not exists in another table

Let’s say we need to list every person which does not have an account.

Thus a person record that does not have a account record.

There’s multiple ways of doing this, each way may be more efficient depending on table sizes.

NOT IN
The easiest but not necessarily the most efficient depending on the account table size.

SELECT *
FROM Persons
WHERE id NOT IN (SELECT person_id FROM accounts)

NOT EXISTS
The title says it, and it’s designed specially for our goal

SELECT *
FROM Persons
WHERE NOT EXISTS
(SELECT person_id
FROM accounts
WHERE accounts.person_id = persons.id)

LEFT OUTER JOIN (IS NULL)
The left outer join is probably quickest in the general case as it prevents repeated execution of the subquery.

SELECT *
FROM Persons
LEFT OUTER JOIN accounts
ON (persons.id = accounts.person_id)
WHERE accounts.person_id IS NULL
Tagged , ,

Leave a Reply