Find Unmatched Records with LEFT JOIN Anti-Pattern in SQL

Question

Given `candidates` (candidate_id, name, email) and `offers` (offer_id, candidate_id, company_name), write a query to find all candidates who have NOT yet received any job offers.

Accepted Answer

Optimal Solution:
SELECT 
  c.candidate_id, 
  c.name, 
  c.email
FROM candidates c
LEFT JOIN offers o ON c.candidate_id = o.candidate_id
WHERE o.offer_id IS NULL
ORDER BY c.candidate_id ASC;

Technical Explanation:
A `LEFT JOIN` preserves all rows from `candidates`. For candidates with no offers, `o.offer_id` evaluates to `NULL`. The `WHERE o.offer_id IS NULL` clause filters for unmatched records.

candidate_id	name	offer_id	company_name
1	Sachin Kumar	901	PwC
2	Shreya Aggarwal	902	HPE
3	Vikram Malhotra	NULL	NULL