RE: Classic trap : using SQL while not fully competent

1st SELECT

Join is done first. For records with no match on the right hand side you
get a NULL value. Then the select criteria executes on the entire joined
result set. Of course, NULL does not equal '14' and you get no hits.

2nd SELECT

Result set is built in the subquery, containing all rows that match '14'.
This result set is compared with all rows in the outer select and of course,
some of the rows match.
This is nothing like the first example. It is not an exception join. In
fact there is no join taking place at all.
I understand that you did this just to convince yourself there is indeed a
row with value '14' in the secondary dial (right-hand table).

3rd SELECT

By putting selection into join criteria, you've told the DB2 engine to first
select any rows in the secondary dial that match a value of '14', and then
join that smaller result set with the primary dial.
This may or may not be what you were after, but you're on the right track
with this solution.

Take a look at the SQL Join Primer article at this link, perhaps it helps:

www.centerfieldtechnology.com/publications

Also, there was a nice article on joins in March SystemiNews magazine as
well (ProVIP edition).

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Classic trap : using SQL while not fully competent

I was going to cry for help :

I know I have a record on the left that ain't on the right so I did

SELECT * FROM prdcap exception join adhtst on ad1cad=tadhno
WHERE tcrtcd ='14'

Which returned no record.


But

SELECT * FROM prdcap WHERE P ad1cad not in
( select tadhno from adhtst where tcrtcd ='14')

Found the record.

After a while I thought, maybe its because tcrtcd is in the right hand
table, so I tried
SELECT * FROM prdcap exception join adhtst on ad1cad=tadhno
and tcrtcd = '14'

Which worked.

Its the first time I put the where criteria in the JOIN. Can anyone give me
the technical explication as to why the first example didn't work so I don't
fall in again please ?

Thanks.