Different Results based on placement of condition in query. Why?

[Staceyd]

I have two (basically similar) queries. The two return different results because of where I have placed the filter for a12.item_nbr = 'm015900706'. I don't understand why this changed the results. Can someone explain.

The first query is as follows:
select a12.ITEM_NBR ITEM_NBR,
a11.PLT PLT,
max(a13.PLANT_NAME) PLANT_NAME,
CAST(a11.CHILD_TIME_STAMP as DATE) CHILD_DATE,
count(distinct (a11.CHILD_NBR || cast(a11.CHILD_TIME_STAMP as char(26)))) WJXBFS1
from CHILD_ROLL a11
left outer join inventory_history a12
on (Cast(Substr(Cast(a11.INV_HIST_SKEY as CHAR(26)), 1, 19) as TIMESTAMP) = Cast(Substr(Cast(a12.INV_HIST_SKEY as CHAR(26)), 1, 19) as TIMESTAMP) and
a11.HIST_INV_ID = a12.HIST_INV_ID)
and a12.item_nbr in ('m015900706')
left outer join PLANT_DESC a13
on (a11.PLT = a13.PLANT_NBR)
where a11.plt in ('34')
and cast(a11.child_time_stamp as date)
= date '2004-02-02'
group by a12.ITEM_NBR,
a11.PLT,
CAST(a11.CHILD_TIME_STAMP as DATE)

Results of query 1:
ITEM_NBR PLT PLANT_NAME CHILD_DATE WJXBFS1
M015900706 34 D.C. 1 2004-04-02 25
? 34 D.C. 1 2004-04-02 2,321

The second query is as follows:
select a12.ITEM_NBR ITEM_NBR,
a11.PLT PLT,
max(a13.PLANT_NAME) PLANT_NAME,
CAST(a11.CHILD_TIME_STAMP as DATE) CHILD_DATE,
count(distinct (a11.CHILD_NBR || cast
(a11.CHILD_TIME_STAMP as char(26)))) WJXBFS1
from CHILD_ROLL a11
left outer join inventory_history a12
on (Cast(Substr(Cast(a11.INV_HIST_SKEY as CHAR
26)), 1, 19) as TIMESTAMP) = Cast(Substr(Cast
a12.INV_HIST_SKEY as CHAR(26)), 1, 19) as
TIMESTAMP)
and a11.HIST_INV_ID = a12.HIST_INV_ID)
left outer join PLANT_DESC a13
on (a11.PLT = a13.PLANT_NBR)
where (a12.ITEM_NBR = 'm015900706'
and a11.PLT in ('34')
and CAST(a11.CHILD_TIME_STAMP as DATE) = DATE '2004-
04-02')
group by a12.ITEM_NBR,
a11.PLT,
CAST(a11.CHILD_TIME_STAMP as DATE)

Results for Query 2:
ITEM_NBR PLT PLANT_NAME CHILD_DATE WJXBFS1
M015900706 34 D.C. 2004-04-02 25

 

[swampBoogie]

where (a12.ITEM_NBR = 'm015900706'

is false when a12.ITEM_NBR is null.

 

[dnoeth]

During an Outer Join the conditions placed in the ON clause are evaluated first and non-matching rows are filled with NULLs.
The WHERE clause is evaluated after that and non-macthing rows are removed from the answer set.

So the first query returns *all* rows for plant number 34 and rows with a condition of ITEM_NBR <> 'm015900706' are NULLed.

That's why outer joins may be quite confusing. Sometimes it's hard to find out if the answer set is the expected one, even after years of writing SQL ;-)

Dieter