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

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?

CodePudding user response：

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
)

CodePudding user response：

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