CODEWITHSUNDEEP
sqlselectjoinleft-join
ps0604
ps0604

Reputation: 1071

Need to understand specific LEFT OUTER JOIN behavior in SQL SELECT

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?

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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.

Upvotes: 2

Related Questions