5 Filtering Tables

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

6 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.

7 Filtering on the ON clause

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

7.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

7.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!

7.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!).

7.4 WHERE EXISTS

WHERE EXISTS isn

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

7.5 Handling NULLs

To record missing and unknown values, SQL uses NULLs. NULL by itself is not a data value but a marker representing the absence of a value. Since NULLs are an unknown value, they will not match any value. That means, they won’t work with joins, in where statements or in case when statements.

7.6 LIKE for fixed length wildcards

When using the LIKE statement in a WHERE clause, * defines any number of characters. _ defines a single character. For example, WHERE name LIKE '_ake' would capture Jake but not Drake.