Anti-joins in SQL

ยท

2 min read

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.

No alt text provided for this image

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

Did you find this article valuable?

Support Constantin Lungu by becoming a sponsor. Any amount is appreciated!

ย