left outer join and *=

[tinac99]

Hi,

I'm trying to retrieve a list from one table joined to the same table with separate aliases. Data in the table is follows:
table1
-------
category subcategory
-------- -----------
112 1
112 2
112 3
112 4
112 5
113 1
113 2


Desired Result:

I would like the query to return those with whose value is category 112 but the subcategory does not exist in value 113. Thus,
category subcategory
-------- -----------
112 3
112 4
112 5

I'm trying to accomplish this using a "left outer join" to the same table:
select z1.category, z1.subcategory, z2.category, z2.subcategory
from table1 z1 left outer join table1 z2 on z1.subcategory = z2.subcategory
where z1.category = 112
and z2.category = 113

However, this query returns the intersection:
112 1 113 1
112 2 113 2


When I run the sql the "old-fashion-way", using an outer join(code: *= ), it's coming up correctly:

select z1.category, z1.subcategory, z2.category, z2.subcategory
from table1 z1 , table1 z2
where z1.subcategory *= z2.subcategory
and z1.category = 112
and z2.category = 113

Result:
category subcategory
-------- -----------
112 3
112 4
112 5


Can somebody point out the differences, please. I thought that the two sqls would return the same thing. And also, can someone tell me what should be the "outer join" equivalent of the last sql?


Thanks a lot,

Tina

 

[SQLSister]

No the left join is correct the other is not. *= is not supported by SQL server and will give wrong answers as it sometimes interprets as a cross join instead of a left join. Never under any circumstances use that construction against a SQl Server database (version 2000 or greater, not sure about earlier versions).
Try

select z1.category, z1.subcategory, z2.category, z2.subcategory
from table1 z1 left outer join table1 z2 on z1.subcategory = z2.subcategory
where z1.category = 112
and (z2.category = 113 or z2.category is null)

"NOTHING is more important in a database than integrity." ESquared

 

[kaht]

Here's an example:

declare @a table (category tinyint, subcategory tinyint)
insert into @a
select 112, 1 union
select 112, 2 union
select 112, 3 union
select 112, 4 union
select 112, 5 union
select 113, 1 union
select 113, 2

select * from @a

select a1.category, a1.subcategory
from @a a1
   left join @a a2
   on a1.subcategory = a2.subcategory
      and a2.category = 113
where a1.category = 112
   and a2.category is null

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[kaht]

As usual in the SQL forum, I am 5 minutes behind everybody else's suggestions

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[tinac99]

Thanks for the suggestions.

I tried:
" and (z2.category = 113 or z2.category is null)" with the left join and the results turn out the same.
kaht's sql worked, though.

I appreciate all your replies.

Thanks,

Tina

 

[ESquared]

kaht's way is the "right" way. There is always the chance that the outer-joined table actually has a null value in that column. That is, unless the "or X is null" has X as the column used in the join, you might unexpectedly get a row that DOES have a match.

I think there could also be a difference in the execution plan. It's better to put the condition as "early" as possible and avoid an extra OR clause.

 

[SQLDenis]

what happens now?

select z1.category, z1.subcategory, z2.category, z2.subcategory
from table1 z1 left outer join table1 z2 on z1.subcategory = z2.subcategory
AND z1.category = 112
and z2.category = 113

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

Now you get all rows from z1 and a couple of rows from z2

[tt]z1 z2
112 1 113 1
112 2 113 2
112 3 NULL NULL
112 4 NULL NULL
112 5 NULL NULL
114 2 NULL NULL
114 4 NULL NULL
115 2 NULL NULL
115 6 NULL NULL
... all rows[/tt]

 

[SQLDenis]

I see I musunderstood the question ;-(

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions