LEFT JOIN with NULL Filtering
LEFT JOIN with NULL Filtering is a SQL clause in the JOIN Types category. Anti-join pattern using LEFT JOIN + IS NULL check. Finds rows in t1 with no match in t2. The syntax is SELECT * FROM t1 LEFT JOIN t2 ON ... WHERE t2.id IS NULL;. It returns rows without matches. A typical example: SELECT e.* FROM employees e LEFT JOIN project_assignments pa ON e.id = pa.employee_id WHERE pa.employee_id IS NULL; -- Employees with NO project assignment -- This is functionally equivalent to: SELECT * FROM employees WHERE id NOT IN (SELECT employee_id FROM project_assignments WHERE employee_id IS NOT NULL); -- But often performs better than NOT EXISTS or NOT IN -- Known as an anti-join pattern A close relative is INNER JOIN, which returns only rows where there is a match in BOTH tables. The most common JOIN type. A close relative is LEFT JOIN, which returns ALL rows from the left table and matching rows from the right. Unmatched right rows show NULL.