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 — Zohar Peled
– Zohar Peled, Commented Aug 13, 2017 at 17:31

Gordon Linoff · Accepted Answer · 2017-08-13 17:31:00Z

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.

Collectives™ on Stack Overflow

Need to understand specific LEFT OUTER JOIN behavior in SQL SELECT

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related