November 22, 2010 at 9:48 am
Hello All,
I’ve read several articles on JOINs and totally understand the theory behind JOIN operations. I use them all the time but I still get a little confused when it comes to choosing the correct JOIN to use.
In all of my tables, I have a foreign key that’s called ResourceID which basically relates to the name of a server. So, ResourceID 001 = server abc, 002 = server dfg, and so on.
I have two questions regarding JOINS.
1) When we talk about LEFT and RIGHT JOINS, does that relate to the left or right table in the ON clause? Example FROM table1 LEFT INNER JOIN table2 ON table1.ResourceID = table2.ResourceID. Is the LEFT table “table1” in this scenario being that it is on the left of the “=” sign in the ON clause?
2) I want every record from table1 and ONLY records from table2 that correspond to records in table 1 and records from table3 that also ONLY correspond to records in table1 and so on. That being the case, what are the correct JOINS to use in that scenario?
Ex: Select tbl1.server, tbl1.location, tbl1.model, tbl2.memory, tbl2.CPU, tbl2.disk,
tbl3.network, tbl3.cables
FROM table1 tbl1
JOIN table2 tbl2
ON tbl1.ResourceID = tbl2.ResourceID
LEFT table3 tbl3
ON tbl1.ResourceID = tbl3.ResourceID
Based on the requirements of only needing records from table2 and table3 that have matching ResourceIDs found in table1. are those the correct JOINs to use to avoid getting all records from each tables?
Thanks in advance for your help!!
Ronnie
November 22, 2010 at 10:20 am
1) There is nothing called the LEFT INNER JOIN. It is always a LEFT OUTER JOIN where the word OUTER is optional. So you can also write the same as LEFT JOIN. In your example the table table1 is the LEFT table.
Consider only this, table1 LEFT OUTER JOIN table2. Here table1 is on the left hand side and hence is the LEFT table. It doesn't depend on the "=" sign in the ON clause. So whether you write the condition as table1.ResourceID = table2.ResourceID or table2.ResourceID = table1.ResourceID, it doesn't matter.
2)Your query should be something similar to the below one
Select tbl1.server, tbl1.location, tbl1.model, tbl2.memory, tbl2.CPU, tbl2.disk,
tbl3.network, tbl3.cables
FROM table1 tbl1
LEFT OUTER JOIN table2 tbl2
ON tbl1.ResourceID = tbl2.ResourceID
LEFT OUTER JOIN table3 tbl3
ON tbl1.ResourceID = tbl3.ResourceID
Hope this helps..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 22, 2010 at 10:33 am
Thank You Kingston!! So, I'm assuming table1 is always the left table because it is the first in the list; is that correct?
Yes, you've helped out tremendously with that information. I really need to understand the joins because I need to use them quite often in my queries.
Thanks again!
Ronnie
November 22, 2010 at 12:23 pm
Thank You Kingston!! So, I'm assuming table1 is always the left table because it is the first in the list; is that correct?
Yes, you've helped out tremendously with that information. I really need to understand the joins because I need to use them quite often in my queries.
Thanks again!
Ronnie
November 22, 2010 at 4:50 pm
Yes, that's right.
You could consider the LEFT/RIGHT table as the "always" side of the JOIN, with the other, un-named side being the "optional" side.
So, back to your q 2), breaking it into two pieces:
2) I want every record from table1 and ONLY records from table2 that correspond to records in table 1
SELECT t1.*, t2.*
FROM table1 t1 --LEFT table, because it's given first
LEFT OUTER JOIN table2 t2 ON t1.whatever = t2.whatever
Note that since you get every row from table1, you will always see the columns from t2. SQL will automatically set every t2 column to NULL if a match in t1 was not found.
Now let's progress to the full 2):
2) I want every record from table1 and ONLY records from table2 that correspond to records in table 1 and records from table3 that also ONLY correspond to records in table1 and so on.
SELECT t1.*, t3.*, t2.*
FROM table1 t1
INNER JOIN table3 t3 ON t1.whatever = t3.whatever
LEFT OUTER JOIN table2 t2 ON t2.whatever = t1.whatever
When possible, you can change the order of the joins to make the code easier to write/maintain/understand.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply