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.
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)
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