Why the ANSI-92 outer join sql statement fails but legacy sql succeed?

[9295]

I have a table, customer. The following is the sql statement and its output:

select C.ID, C.FNAME, C.LNAME, C.DATE
FROM CUSTOMER C
WHERE C.ID = 1007

3 John Smith 2005-10-05 00:00:00.000
4 Mary Brown NULL
5 Bill White NULL

And another table, account_detail, with about 30 records in there.

The following is the sql statement to join these two tables and its output:

select C.ID, C.FNAME, C.LNAME, AD.AMOUNT AS BONUS
from CUSTOMER C join account_detail AD on (C.ID = AD.REFERENCE)
where C.ID = 1007 and AD.TRANS_TYPE = 'BR'

3 John Smith 10.0000

In order to include all record in customer table, I used an outer join. Here is the sql statement and it out put:

select C.ID, C.FNAME, C.LNAME, AD.AMOUNT AS BONUS
from CUSTOMER C left join account_detail AD on (C.ID = AD.REFERENCE)
where C.ID = 1007 and AD.TRANS_TYPE = 'BR'

3 John Smith 10.0000

The output is exactly the same as the one from previous inner join. I added an 'outer' to make it a "left outter join" but the output remained the same.

I changed to use the legacy outer join as the following:

select C.ID, C.FNAME, C.LNAME, AD.TRANS_AMOUNT AS BONUS
from CUSTOMER C, account_detail AD
where C.ID *= AD.REFERENCE and C.ID = 1007 and AD.TRANS_TYPE = 'BR'

and I got the right result:
3 John Smith 10.0000
4 Mary Brown NULL
5 Bill White NULL

Can anyone tell me why the legacy outer join statement works but the ANSI-92 outer join sql statement fails?

Did I do something wrong or I fail to set up some system configuration?

Your help is much appreciated!

 

[NoCoolHandle]

Where are you getting and AD.TRANS_TYPE = 'BR' from?

If it's not there, it won't return it... (even thouth you have specified a left outer join..

Bottom line.. This is your issue as that is forcing the ansi join to find a value from the Account Detail table.


Rob

PS an alternative (I think it should work) is to add some of the criteria to the from clause.....

select C.ID, C.FNAME, C.LNAME, AD.AMOUNT AS BONUS
from CUSTOMER C left join account_detail AD on (C.ID = AD.REFERENCE and AD.TRANS_TYPE = 'BR')
where C.ID = 1007

 

[NoCoolHandle]

maybe get rid of the parens... I will try to test on some sample data...

Rob

 

[RoyVidar]

I think that when you've got a criterion on some of the fields in the table where Nulls can occur, your above syntax might be closer to a inner join, perhaps in stead something like this

[tt]select C.ID, C.FNAME, C.LNAME, AD.AMOUNT AS BONUS
from CUSTOMER C join account_detail AD on (C.ID = AD.REFERENCE)
where C.ID = 1007 and
(AD.TRANS_TYPE = 'BR' OR AD.TRANS_TYPE Is Null)[/tt]

Roy-Vidar

 

[SQLDenis]

select C.ID, C.FNAME, C.LNAME, AD.AMOUNT AS BONUS
from CUSTOMER C left join account_detail AD on C.ID = AD.REFERENCE
and AD.TRANS_TYPE = 'BR'
where C.ID = 1007

You can not put AD.TRANS_TYPE = 'BR' in the where clause because you are basically constructing a regular join by having everything after the WHERE


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[9295]

Dear All,

Trans_type comes from account_detail and is not null.

I have tried both NoCoolHandle and SQLDenis' suggestions and they both work GREAT. I got the right answer. However I still DON'T quite understand why. Do you have any good articles or books which you will recommend on this issue?

Thank you all so much.

9295

 

[vongrunt]

Simple rule to remember: WHERE clause applies after JOINs.

So... imagine (or draw or try, whatever) what happens with JOIN conditions only. Then add WHERE clause. Simple.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[r937]

Trans_type comes from account_detail and is not null.

yes, but in this LEFT OUTER JOIN, account_detail is the right table, which means that if there is an unmatched customer, i.e. a left row without a matching right row, then all the columns from the right table will be null in the intermediate result set produced by the join


so if the WHERE clause then stipulates AD.TRANS_TYPE = 'BR' then this row (customer with no matching account) is thrown away, because NULL is not equal to 'BR'

r937.com | rudy.ca