Need to understand specific LEFT OUTER JOIN behavior in SQL SELECT

Question

I have two tables, transactions and dates. One date may have one or more transactions. I need to get a list of dates with or without transactions of a specific account (account number 111 below).

select d.the_date, t.account, t.amount from dates as d
LEFT OUTER JOIN transactions as t ON t.tx_date=d.the_date
where t.account=111 AND d.the_date>='2016-01-02' and d.the_date<='2017-12-30'
order by d.the_date;

The issue is that when I specify in the condition t.account=111 I don't get the dates on which account 111 did NOT make any transactions.

Only if I remove from the condition t.account=111 I DO get the dates with no transactions (i.e. the LEFT OUTER JOIN works). Why does this happen?

Possible duplicate of [SQL Different between Left join on... and Left Join on..where](https://stackoverflow.com/questions/44696051/sql-different-between-left-join-on-and-left-join-on-where) — Zohar Peled, Aug 13 '17 at 17:31

score 2 · Accepted Answer · answered Aug 13 '17 at 17:31

Conditions on the second table need to go into the on clause:

select d.the_date, t.account, t.amount
from dates d left join
     transactions t 
     on t.tx_date = d.the_date and t.account = 111
where d.the_date >= '2016-01-02' and d.the_date <= '2017-12-30'
order by d.the_date;

Otherwise, the value of t.account is NULL in the where clause, turning the outer join into an inner join.

Need to understand specific LEFT OUTER JOIN behavior in SQL SELECT

1 Answers1