Chapter 105. Calculating ARPU
When I run Query 1 query, I expected the query will return me only rows with refunded = False. But it also returns rows with refunded = Null.
QUERY 1
SELECT *
FROM users u
LEFT JOIN purchases p
ON u.id = p.user_id
AND refunded = FALSE
QUERY 2
But when I run query 2, it returns me rows with refunded = false (no null rows):
SELECT u.id, status, refunded
FROM users u
LEFT JOIN purchases p
ON u.id = p.user_id
WHERE
refunded = FALSE
Can someone please explain me this?
REPLIES
Hey @vrushali 
Great question here! I think it’s time to start linking these questions together and maybe bring them to the correspondent lesson/exercise pages. 
Here’re some of related forum questions:
- https://www.sqlhabit.com/forum/questions/where-to-use-and-refunded-false-on-arpu-and-arppu
- https://www.sqlhabit.com/forum/questions/lesson-107-arppu-calculations-not-quite-the-same
- https://www.sqlhabit.com/forum/questions/filter-of-refunded-values-in-arpu-calculation
Anyways, happy to help here, let’s get to work! I feel like this is a cornerstone of deep understanding of LEFT JOIN-s and filters.
Clearly, the only difference between Query 1 and Query 2 is the position of the filter refunded = FALSE. That position (in the JOIN vs in the WHERE clause) determines when this filter is applied.
In Query 1 you can think that we first filter out refunded purchases and join smaller purchases table to the entire users table.
In Query 2 we first join all purchases to all users (LEFT JOIN ensures all users and purchases records will be in the result set of that join). After the join is done, we filter out all joined rows where refunded = FALSE.
The problem here is that for users without purchases refunded column will contain no value (NULL), so these records will be filtered out as well (since we apply refunded = FALSE to the entire joined result set).
Hope that helps 