Question about joins - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Question about joins

Author

Topic

deepugun
Starting Member

11 Posts

Posted - 2013-06-11 : 11:39:09

Hello All,
I am actually trying to write a multi table join where i need every thing from a specific table(i.e. Test_copy) and need the joined data from the rest of the two tables.
Select count(*) Test_copy
460699

select COUNT(*)
from Test_COPY TC
join Test_Role TR
ON TC.Test_Role_Id = TR.Test_Role_Id
JOIN address adr
ON adr.Address_Id = CR.Address_ID

453018

In order to get all the records from the Test_copy, I assumed that left outer join on Test_copy would suffice but in reality it did not change the row count i.e.

select COUNT(*)
from Test_COPY TC
left outer join Test_Role TR
ON TC.Test_Role_Id = TR.Test_Role_Id
JOIN address adr
ON adr.Address_Id = CR.Address_ID

453018

but when i run the below query it returned me the right row count

select COUNT(*)
from Test_COPY TC
left outer join Test_Role TR
ON TC.Test_Role_Id = TR.Test_Role_Id
left outer JOIN address adr
ON adr.Address_Id = CR.Address_ID

460699

It would be helpful if some one could clarify this behavior i.e. why do i need left outer joins next to the two tables?
Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 12:34:37

the reason is because table address is related by means of the intermediate table (TestRole). So any rows which lack matching values in TestRole will not be able to find a match in address table either. Hence unless you make it left join you'll miss out all those records having no match on other tables.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

neeraj_sharma
Starting Member

15 Posts

Posted - 2013-06-12 : 04:44:08

IN Addition to Answer

Join performs Left to Right means From to End of join.

so in your query database engine performing left join first then
after its result joining to the third table.

to verify that database engine really performing it Left to right.

Here is example below :

Create table #table1 (ID int )
Create table #table2 (ID int )
Create table #table3 (ID int )

insert into #table1
Select 1 union
Select 2 union
Select 3

insert into #table2
Select 1 union
Select 2

insert into #table3
Select 1

Select count(*) From #table1 -- result 3

Select count(*) From #table1 T1 Join #table2 T2 ON -- result 2
T1.ID = T2.ID

Select count(*) From #table1 T1 LEFT Join #table2 T2 ON -- result 2
T1.ID = T2.ID

Select count(*) From #table1 T1 LEFT Join #table2 T2 ON -- RESULT 1
(T1.ID = T2.ID )
Join #table3 T3 ON (T2.ID = T3.ID)

-- your quert
Select count(*) From #table1 T1 LEFT Join #table2 T2 ON -- RESULT 1
(T1.ID = T2.ID )
Join #table3 T3 ON (T1.ID = T3.ID)

Select count(*) From #table2 T2 Join #table3 T3 ON -- RESULT 3
(T2.ID = T3.ID )
RIGHT Join #table1 T1 ON (T1.ID = T3.ID)

www.tutorialsqlserver.com
[url][/url]

@nrj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources