Here's the SQL Anti-Join, another type of join that doesn't have its own keyword but it's very much a thing. You might have used it a lot of times before ๐
So, what does it do?
The anti-join retains all the rows in one table that are not also found in another table. So A \ B.
We achieve this with a LEFT JOIN + WHERE [key in right table] IS NULL for the LEFT ANTI JOIN and RIGHT JOIN + WHERE [key in left table) IS NULL for the RIGHT ANTI JOIN (although I know right joins don't get much love here ๐).
An anti-join is a bit similar to the EXCEPT set operation, with the difference that:
- in the EXCEPT if at least one column is different between the two tables, the row is considered a difference (so is kept)
- in the ANTI JOIN we typically look at just whether the join keys are present in the other table or not (but you check as many columns as you want, of course)
In the example below, we're illustrating a LEFT ANTI JOIN which finds all the products we have details for (like product name) but for which we don't have a row in the pricing table.
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.