Left join oracle db

Question

I have two tables and I wanna join them (outer).

day  description
-----
1     mo
2     tu
...
5     fr



order   day     code
----      
1       1         0
2       2         0
3       1         2
4       3         0
5       4         0

I wanna this table as a result:

description       order         code 
------
mo                  1           0
th                  2           0 
we                  4           0
tu                  5           0
fr                  null        null

When I use a left join like this, the code does not work right and does not show me friday:

select * 
from days d left join order o on d.id= o.day
where o.code = 0

But when I write the code like this, the code works right:

select *
from 
(select * from day) d
left join
(select * from order when code = 0) o
on d.id = o.day

Does anyone know why?

[Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 10 '20 at 00:22

score 0 · Accepted Answer · answered Jun 09 '20 at 12:50

Just put the condition on the left joined table in the on clause of the left join rather than in the where clause:

select d.description, o.order, o.code
from days d 
left join order o on d.id= o.day and o.code = 0

Conditions in the where clause are mandatory - so the where clause eliminates rows where the left join came back empty, since o.code is null, which is not equal to 0.

score 0 · Answer 2 · answered Jun 09 '20 at 12:56

on condition is used to join the table. where condition is to filter the records from the join result.

Below will join the table on the condition of d.id = o.day and then select records only with o.code=0

select * 
from days d left join order o on d.id= o.day
where o.code = 0

In order to join on both d.id = o.day and o.code=0 you need below

select * 
from days d left join order o on d.id= o.day
and o.code = 0

Left join oracle db

2 Answers2