SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question about joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deepugun
Starting Member

10 Posts

Posted - 06/11/2013 :  11:39:09  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 06/11/2013 :  12:34:37  Show Profile  Reply with Quote
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
Go to Top of Page

neeraj_sharma
Starting Member

India
15 Posts

Posted - 06/12/2013 :  04:44:08  Show Profile  Reply with Quote
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


@nrj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000