Join understanding (Aint urgent or Very Imp)

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)

Join understanding (Aint urgent or Very Imp)

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2009-04-20 : 11:49:49

Hi Guys,

This aint anything important, but its some thing I came across and has left me wondering.

create table tab1 (a int, A1 VARCHAR(MAX));

create table tab2 (b int, B1 VARCHAR(MAX));

create table tab3 (c int, C1 VARCHAR(MAX));

insert into tab1 values (1,'TAB11');
insert into tab1 values (2,'TAB12');
insert into tab1 values (3,'TAB13');
insert into tab1 values (4,'TAB14');

insert into tab2 values (1,'TAB21');
insert into tab2 values (2,'TAB22');

insert into tab3 values (1,'TAB31');
insert into tab3 values (2,'TAB32');


SET SHOWPLAN_TEXT ON

select A,a1,b1,c1 from tab1 left join tab2 on a = b INNER join tab3 on b = c;

  |--Hash Match(Inner Join, HASH:([TESTERP].[dbo].[tab3].[c])=([TESTERP].[dbo].[tab2].), RESIDUAL:([TESTERP].[dbo].[tab2].[b]=[TESTERP].[dbo].[tab3].[c]))
       |--Hash Match([b]Inner Join, HASH:([TESTERP].[dbo].[tab3].[c])=([TESTERP].[dbo].[tab1].[a]), RESIDUAL:([TESTERP].[dbo].[tab1].[a]=[TESTERP].[dbo].[tab3].[c]))
       |    |--Table Scan(OBJECT:([TESTERP].[dbo].[tab3]))
       |    |--Table Scan(OBJECT:([TESTERP].[dbo].[tab1]))
       |--Table Scan(OBJECT:([TESTERP].[dbo].[tab2]))

Now this was a very simple example, where in wanted all the records from tab1, and corersponding records from tab2 and tab3.
But the plan says that both the joins in the query happens to be an INNER JOIN, and not 1 inner and 1 left outer.

Why does SQL totally ignore the left join?
I feel my understanding of joins aint solid and there has to be a logical reason for this, if anyone could throw some light on this, I would really appreciate it.

Thanks
Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-20 : 12:16:14

If you look carefully you'll notice that the optimizer is INNER JOINing Tab3 to Tab1 on A=C. It had probably determined that rewriting that join will be more efficient. One way to test it is to run:

select A,a1,b1,c1 from tab1 left join tab2 on a = b INNER join tab3 on a = c;

And see if you get the same plan. By the way, did it return the correct results?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-20 : 12:49:48

In addition to what Rob said. In this instance the LEFT OUTER JOIN doesn't really mean anything since you are INNER JOINing later on the same column as the LEFT OUTER JOIN. So, in essence you have turned the LEFT OUTER JOIN into an INNER JOIN and, as Rob mentioned, the optimizer was/is smart enough to realize that and join accordingly. Hopefully, that makes sense.. :)

xpandre
Posting Yak Master

212 Posts

Posted - 2009-04-20 : 12:54:00

No.The resultset was not as I expected. It never did the left join.

I expected;

1 TAB11 TAB21 TAB31
2 TAB12 TAB22 TAB32
3 TAB13 NULL NULL
4 TAB14 NULL NULL

But it gave:

1 TAB11 TAB21 TAB31
2 TAB12 TAB22 TAB32

Thanks
Sam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-20 : 13:44:04

Ahh, in order to get your desired results you need to chage the INNER JOIN to a LEFT OUTER JOIN:

select A,a1,b1,c1 from tab1 left join tab2 on a = b LEFT OUTER JOIN tab3 on b = c

The INNER JOIN is restricting the results to, well the intersection of the LEFT OUTER JOIN and the INNER JOIN, thus why the NULLS are getting filtered out. Does that make sense?

xpandre
Posting Yak Master

212 Posts

Posted - 2009-04-21 : 03:18:01

Thanks Lamprey.

Here is what I did this morning:
This is the old statement:

select a,b,c from tab1 left join tab2 on a = b inner join tab3 on b = c;

StmtText
-------------------------------------------------------------------------
select a,b,c from tab1 left join tab2 on a = b inner join tab3 on b = c;

(1 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([dbo].[tab3].[c])=([dbo].[tab2].[b]), RESIDUAL:([dbo].[tab2].[b]=[dbo].[tab3].[c]))
|--Hash Match(Inner Join, HASH:([dbo].[tab3].[c])=([dbo].[tab1].[a]), RESIDUAL:([dbo].[tab1].[a]=[dbo].[tab3].[c]))
| |--Table Scan(OBJECT:([dbo].[tab3]))
| |--Table Scan(OBJECT:([dbo].[tab1]))
|--Table Scan(OBJECT:([dbo].[tab2]))

(5 row(s) affected)

This is the altered statement, I have just changed the oder of join here.
Now it does what I want.

select a,b,c from tab2 inner join tab3 on b = c right join tab1 on a = b

StmtText
-------------------------------------------------------------------------
select a,b,c from tab2 inner join tab3 on b = c right join tab1 on a = b

(1 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([dbo].[tab1].[a]))
|--Table Scan(OBJECT:([dbo].[tab1]))
|--Nested Loops(Inner Join)
|--Table Scan(OBJECT:([dbo].[tab2]), WHERE:([dbo].[tab1].[a]=[dbo].[tab2].[b]))
|--Table Scan(OBJECT:([dbo].[tab3]), WHERE:([dbo].[tab3].[c]=[dbo].[tab1].[a]))

(5 row(s) affected)

I feel I got myself confused on the ordering of Joined tables. I would need to read more on this.

Thanks
Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources