asked on

Oracle 10g - LEFT join not returning null rows

I have a simple left join query that is not giving the expected output. I am hoping a second set of eyes can see what I am missing

My main table should return all rows:

select LOC_CODE, LOC_STAT, from M_LOC where comp_Code = 'A1' and Whse_code = '8' and loc_code like '28-%'

28-11-01	A
28-11-02	A
28-11-03	A
28-11-04	A
28-11-05	A

Open in new window

My second table contains only records matching 2 of the 5 rows

select LOC_CODE, Cust_code from C_LOC where comp_Code = 'A1' and Whse_code = '8' and loc_code like '28-%'

28-11-01	BVFTOR
28-11-03	BVFTOR

Open in new window

My Query with the LEFT Join is behaving like an EQUAL join and only outputting records that exist in the second table

select 
M.LOC_CODE,
C.CUST_CODE
from M_LOC M
LEFT OUTER JOIN C_LOC C on M.COMP_CODE = C.COMP_CODE and M.WHSE_CODE = C.WHSE_CODE and M.LOC_CODE = C.LOC_CODE 
where M.comp_Code = 'A1' and M.Whse_code = '8' and M.loc_STAT = 'A'
and M.loc_code like '28-%'

28-11-01	BVFTOR
28-11-03	BVFTOR

Open in new window

What am I overlooking in this simple query?

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JDCam

ASKER

thank you.... you just gave me a clue and now see the issue.
I am showing you only the top 5 rows of 10,000+
the null records are there, they are just at the bottom.
I added a Order by LOC_CODE and the output is correct.

Alex [***Alex140181***]

I have to agree: it seems to be correct... Have you tried using parentheses like this:

select 
M.LOC_CODE,
C.CUST_CODE
from M_LOC M
LEFT OUTER JOIN C_LOC C on (c.COMP_CODE = m.COMP_CODE and c.WHSE_CODE = m.WHSE_CODE and c.LOC_CODE = m.LOC_CODE) 
where M.comp_Code = 'A1' and M.Whse_code = '8' and M.loc_STAT = 'A'
and M.loc_code like '28-%'

Open in new window