Left Join with all rows from the left not matching the Where Clause

Question

Hi,

I have the following problem:

I have an account table and an entries for account table. account_id account_name

entry_id
account_idfk
entry_date
entry_amount

Now I want to query all entries for all accounts in a given period. Eg. I want all Entries for all accounts from October 2008 - October 2009. If there are no entries for this account at all, or there are only entries in other timeperiods for this account, I want the account returned as well.

My current query works, if there are no entries at all, or there are entries for this timeperiod for this account. However - it leaves out the Accounts which have only entries for other timeperiods.

SELECT * FROM Account a
         LEFT JOIN Entries e ON e.account_idfk = a.account_id
         WHERE e.entry_date BETWEEN '2009-08-13' AND '2009-08-13'
               OR e.entry_date IS NULL

I know that the problem is in the where clause - I eliminate all Accounts for which only entries in other time periods exist.

But I have no idea how to restate the query to get the desired result...

Thanks, Martin

Answer 1

Move that condition to the join:

SELECT
    * 
FROM 
    Account a
    LEFT JOIN Entries e ON 
        e.account_idfk = a.account_id
        AND e.entry_date BETWEEN '2009-08-13' AND '2009-08-13'

What you see here is the difference between a join and a where condition. The join will only join rows that meet that condition. However, with a left join, you still return all the rows in the left table. With the where clause, you're filtering rows after the join. In this case, you only want to join entries where the date is 8/13/09 (or 13/8/09, for those across the pond), but you want to return all accounts. Therefore, the condition needs to go into the join clause, and not the where.

This often gets confused with any outer join, because with an inner join, the result is the same no matter if the condition is in the join or where clause. However, this does not mean that they are equivalent, as demonstrated by you today!