12  Filtering

Everyone starts out with filtering using the WHERE clause but there’s more to it than just specifying columns and their conditions.

13 Anti joins

Anti joins are a type of join where you return only rows that do not match any row in a given table. For example, let’s say you have users who have done one time purchases but who have no subscription service. You want to send a marketing email to users who don’t have a subscription service yet but have done a one time purchase.

14 Filtering on the ON clause

You can use the ON clause in joins to do neat things, such as:

14.1 Joining only to certain rows in the main table

The left join clause and created_channel = 2 ensures that the join only happens

select 
  users.*,
  subscribers.subscription_type
from users
left join subscribers
  on user_id = users.id
  and created_channel = 2
where exists (select * from subscribers where users.id = subscribers.user_id)
limit 10

14.2 Joining only certain rows from another table

select 
  users.*,
  subscribers.subscription_type
from users
left join subscribers
  on user_id = users.id
  and subscription_type = 2
where exists (select * from subscribers where users.id = subscribers.user_id)
limit 10

If you’re reading this book, you know about the WHERE clause. But there are multiple others ways to filter values!

14.3 Anti joins

I love anti joins because they’re extremely useful for a wide range of scenarios but SQL practitioners don’t always learn about them early on (I certainly didn’t!).

Add anti-join from here: https://github.com/gadenbuie/tidyexplain

14.4 WHERE EXISTS

WHERE EXISTS isn

https://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join