ANTI JOIN

ANTI JOIN is a SQL clause in the JOIN Types category. Returns rows from the left table where NO match exists in the right table. The syntax is SELECT * FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table1.col = table2.col);. It returns rows from left table without matches. A typical example: SELECT * FROM employees e WHERE NOT EXISTS (SELECT 1 FROM projects p WHERE p.lead_id = e.id); -- Employees who are NOT project leads 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. A close relative is RIGHT JOIN, which returns ALL rows from the right table and matching rows from the left. Opposite of LEFT JOIN.