Yet another Left Outer Join Post

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Yet another Left Outer Join Post

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-08-27 : 14:15:56

Does this query make sense to anyone:?

select
  *
from
  Table1, Table2
where
  Table1.C1 = Table2.C1 and
  Table1.C2 *= Table2.C2

This is like joining to a Table with both an inner join and a Left Outer Join at the same time. Any ideas if this can be converted to the new Ansi Join syntax?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-27 : 14:23:57

This doesn't appear to be allowed with the old style as you've written it. I received this message:


declare @table1 table (c1 int, c2 int)
declare @table2 table (c1 int, c2 int)

insert @table1
select 1,1 union all
select 1,2 union all
select 2,1

insert @table2
select 1,1

select
  *
from
  @Table1 t1, @Table2 t2
where
  t1.C1 = t2.C1 and
  t1.C2 *= t2.C2

Msg 303, Level 16, State 1, Line 12
The table '@Table2' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

Be One with the Optimizer
TG

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-08-27 : 14:32:48

Something is strange.

I have seen that error before too, I think. But I copied and pasted your code and I get:

c1	c2	c1	c2
1	1	1	1
1	2	NULL	NULL
2	1	NULL	NULL

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-27 : 14:45:21

Must be the version. I tried it on several "old" boxes with the error result. Here is the version I tried last:


select @@version

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
	May  3 2005 23:18:38 
	Copyright (c) 1988-2003 Microsoft Corporation
	Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Be One with the Optimizer
TG

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-08-27 : 15:12:15

I get:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

But it's running in Compatability Level:
SQL Server 2000 (80)

Maybe that still makes a difference.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-27 : 15:22:04

Ok - after going to a 2005 box and setting the compatibility level back to 80 I didn't get the error either.

These returned the same results - I guess that makes it a regular left outer join:


declare @table1 table (c1 int, c2 int)
declare @table2 table (c1 int, c2 int)

insert @table1
select 1,1 union all
select 1,2 union all
select 1, null union all
select 2,1

insert @table2
select 1,1 union all
select 2,1 union all
select 2, null

select
  *
from
  @Table1 t1, @Table2 t2
where
  t1.C1 = t2.C1 and
  t1.C2 *= t2.C2

select *
from   @table1 t1
left outer join @table2 t2
       on t2.c1 = t1.c1
       and t2.c2 = t1.c2

output:
c1          c2          c1          c2
----------- ----------- ----------- -----------
1           1           1           1
1           2           NULL        NULL
1           NULL        NULL        NULL
2           1           2           1


c1          c2          c1          c2
----------- ----------- ----------- -----------
1           1           1           1
1           2           NULL        NULL
1           NULL        NULL        NULL
2           1           2           1

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources