The SQL Anti-Join is a type of join that doesn't have its own keyword but is commonly used. It retains all rows in one table that are not found in another table, effectively performing an operation similar to ( A \setminus B ).
How it Works
- LEFT ANTI JOIN: Achieved with
LEFT JOIN
+WHERE [key in right table] IS NULL
. - RIGHT ANTI JOIN: Achieved with
RIGHT JOIN
+WHERE [key in left table] IS NULL
.
Comparison with EXCEPT
- EXCEPT: Considers a row different if at least one column differs between the two tables.
- ANTI JOIN: Typically checks if the join keys are present in the other table, though you can check multiple columns.
Example
A LEFT ANTI JOIN can be used to find all products with details (like product name) but without a corresponding row in the pricing table.