FULL JOIN with Filter

FULL JOIN with Filter is a SQL clause in the JOIN Types category. FULL JOIN with IS NULL filter finds rows in EITHER table without a match (symmetric difference). The syntax is SELECT COALESCE(t1.id, t2.id) AS id FROM t1 FULL JOIN t2 ON t1.id = t2.id WHERE t1.id IS NULL OR t2.id IS NULL;. It returns unmatched rows from both tables. A typical example: SELECT COALESCE(e.id, d.id) AS orphan_id, CASE WHEN e.id IS NULL THEN 'Only in departments' ELSE 'Only in employees' END AS source FROM employees e FULL JOIN departments d ON e.dept_id = d.id WHERE e.dept_id IS NULL OR d.id IS NULL; -- Finds: -- 1. Employees with no matching department -- 2. Departments with no employees -- Also known as: symmetric anti-join A close relative is INNER JOIN, which returns only rows where there is a match in BOTH tables. The most common JOIN type.