May 5, 2011 at 4:42 pm
I was wondering about how a JOIN works for 3 or more table.
I understand that a LEFT JOIN will get all selected rows from the first (left side) table and only get matching rows from the second table. My question is...when i want data out of a third or forth table does using a LEFT JOIN join on the original table or the second table...and so on for more tables?
Thanks
May 5, 2011 at 4:48 pm
LEFT JOIN is correct assuming that you wanted all the tables FROM the Left side.
If you used an INNER or RIGHT JOIN then you may not get the intended result depending upon what Data you need from what tables.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2011 at 12:35 am
My understanding is that you are asking which one is correct:
SELECT .... FROM TABLE_A A
LEFT JOIN TABLE_B B
ON A.Id=B.ID
LEFT JOIN TABLE_C C
ON A.Id2=C.Id2
Or
SELECT .... FROM TABLE_A A
LEFT JOIN TABLE_B B
ON A.Id=B.ID
LEFT JOIN TABLE_C C
ON B.Id2=C.Id2
There is no answer to that question, it depends on what are you trying to achieve.
It depends on your tables' structure, what should be the outcome(resultset) etc.
The best is to create a few test tables and play with combinations to understand the difference.
(Tip: Be extra cautious and make sure to test and understand cases with WHERE clause.)
May 6, 2011 at 1:12 am
[font="Tahoma"]
First of all, Left Join does not return only the matched records. It returns all the records from the Table which is on the Left of the Join along with the matched records on the right side table. The unmatched records from the right side table would be returned as NULL..
And, regarding the working of Join...Lets take the below query as an example
Select * from TableA A Join TableB B on A.x = B.x
Join TableC C on B.y = C.y
Join TableD D on D.z = C.z
The records of the TableD would be joined with the output of the previous joins ie. TableA, TableB, TableC which are on the left side of the join operator
The records of the TableC would be joined with the output of the joins TableA,TableB.
The records of the TableB would be joined with TableA
[/font]
May 6, 2011 at 7:26 am
Thanks rmkmurali...
This is exactly what i was confused about. I was thinking that a LEFT JOIN on table C only joined table A or table B...not both. That that makes way more sense now.
Thanks!
May 6, 2011 at 7:33 am
Select * from TableA A Join TableB B on A.x = B.x
Join TableC C on B.y = C.y
Join TableD D on D.z = C.z
Don't you mean a LEFT JOIN?
SELECT *
FROM TableA AS A
LEFT JOIN TableB AS B ON A.X = B.X
LEFT JOIN TableC AS C ON B.y = C.y
LEFT JOIN TableD AS D On D.z = C.z
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2011 at 8:37 am
[font="Tahoma"]
Hello Welsh,
Its doesn't really matter if its an INNER JOIN or LEFT JOIN or RIGHT JOIN. Every column in the tables joined would be returned if you simply use * in the select. You can return the selected columns by mentioning the column names with table alias.
[/font]
May 6, 2011 at 9:16 am
It doesn't really matter, how did you come to that conclusion?
If it does not matter why are there several types of JOINS?
It does matter what type of join it is and it does not depend on whether you perform a SELECT * or not.
SELECT * will not return all columns from a table but if you use an INNER JOIN you will not get the columns and rows that you are looking for.
You may want to avoid a SELECT * for it has an potentially adverse impact on performance.
LEFT OUTER JOIN
Returns all the rows from the left table and the matching rows from the right table. If there are no matching records in the right table, it returns NULL values.
Credit: Pinal Dave
Check out the following URL's, perhaps they may be helpful:
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins
http://www.mssqltips.com/tip.asp?tip=1667
Have you experimented with creating a few tables and testing?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2011 at 9:46 am
I have a pretty good understanding of how joins work on 2 tables. I'm asking about 3 or more tables.
Does the 3rd tables JOIN use results of the first 2 tables JOIN or does it just directly join on the 2nd table and so on with more tables? Make sense?
Thanks
May 6, 2011 at 10:17 am
If you want to perform a LEFT OUTER JOIN on 3 Tables position the tables to the left and you will get return all three tables in the result set.
Have you tried creating and executing queries using the solutions that have been suggested to you?
Make sure that you have tables in the correct order based upon whether you use an LEFT OUTER JOIN or a RiGHT OUTER JOIN.
Personally, I would validate the solution provided from all sources so that you can make your own determination.
Do you have 3 tables or more that you can experiment with. You should give it a shot and we can go from there. It is not very hard and it would not take much effort on your part.
Provide you tables and scripts to populate them show example code that you have created.
If you do doubt what you are told by someone then would suggest that you try Advanced Goggle but if you read the first article that I have posted that should be enough for you to grasp the concept of JOINS.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 8, 2011 at 11:41 pm
I think that I got what you mean.
Please run the from the attachment and you will see that it is all the same.
there will be no difference in the result set, so the order how it is joined will, I believe, depend on the number of records, statistics and other things that engine takes into account when calculating the most optimal execution plan.
I would appreciate if you could let me know whether this answers your question.
Thanks!
P.S. Please add drop table statements, seems that I omitted them from the code that is attached, sorry for that
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply