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

mysql, sql

Solution

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!

Problem

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

Original source