How can I find all unmatched records across 2 tables in SQL?

user_239879 0
Viewed 108

I have:

SELECT * 
FROM recruiter r 
WHERE NOT EXISTS (
  SELECT * FROM recruiter_assigned_role rar 
  WHERE recruiter_assigned_role.recruiterUser = recruiter.userId
)

I want to find all recruiter entries that do not have an associated recruiter_assigned_role. But this query gives me an error:

17:07:52 Kernel error: ERROR:  
invalid reference to FROM-clause entry for table "recruiter_assigned_role"

What am I doing wrong?

postgresql sql
2 Answers

Postgres is expecting you to use the defined table's alias:

SELECT * 
FROM recruiter r 
WHERE NOT EXISTS (
  SELECT * FROM recruiter_assigned_role rar 
  WHERE rar.recruiterUser = r.userId
)
user_153326500

Your query is syntactically incorrect. Always use joins when you can, it's simpler, faster and easier to read.

SELECT r.* 
FROM recruiter r 
LEFT JOIN recruiter_assigned_role rar 
  on (r.userId = rar.recruiterUser)
WHERE
  rar.recruiterUser is null
user_136668250
Related