When doing a left join in SQL any filtering of the table after the join will turn it into an inner join. However there are some easy ways to do the filtering first.
Suppose you've got some tables related to a website.
pages table describes the different pages on the site.
pageviews describes the daily activity:
We want to see the page views with the page name for all the pages on a certain date, even the ones with no views. One wrong attempt would be:
select page_name, coalesce(views, 0) as views from pages left join pageviews on pages.page_id = pageviews.page_id where date = '2020-08-27'
The problem is that the where clause will filter out the terms page from the results.
This could be fixed by moving the where clause into a subquery:
select page_name, coalesce(views, 0) as views from pages left join ( select * from pageviews where date = '2020-08-27' ) pv on pv.page_id = pages.page_id
This is verbose, but gives the correct result.
A very useful trick is to move the filter condition into the join condition.
select page_name, coalesce(views, 0) as views from pages left join pageviews on pages.page_id = pageviews.page_id and date = '2020-08-27'
on effectively filters before the output this gives the same result as the subquery:
Adding filter conditions with
on is useful for simplifying filters like this.
It's also sometimes clearer to put filter conditions closer to the table they apply to rather than at the end in a where clause.