Joins in SQL Server
-
Dear Readers,
I have a question for you all for which I didn't get a suitable answer
on the net. I will really appreciate if somebody can help me out in this.
Question:
==========
We all know what left join and right join means.
Let's suppose we have a HR application which contains tables Employees
and Projects and some other tables too.
Now let's say...I need to find out the information about all employees, their projects, their
Manager names, and the start date of the project and and date of the project.
I write a query like this...
Select emp.EmployeeName, proj.ProjectName, emp.ManagerName, proj.StartDate, proj.EndDate
FROM Employees emp
LEFT JOIN Projects proj
ON emp.EmployeeID = proj.EmployeeID
This will return me all records from Employees tables and only matching records
from Projects table. This will also show those employees who aren't allocated to any projects.
I can modify the above query by RIGHT JOIN (ing) the Employees table with Projects table to get the same result.
Then why do we have two different join types when I can use either a LEFT JOIN or a RIGHT JOIN to suffice all situations.
Can somebody clarify on this??
TIA,
Debsoft
-
It is a matter of the order of evaluation and ease of understanding.
Table1 LEFT JOIN Table2 can easily be re-written as Table2 RIGHT JOIN Table1 but when more than two tables are involved it becomes more complex. eg
SELECT *
FROM Table1 T1
LEFT JOIN (
Table2 T2
JOIN Table3 T3
ON T2.T2PK = T3.T2PK
) ON T1.T1PK = T2.T1PK
is the same as:
SELECT *
FROM Table3 T3
JOIN Table2 T2
ON T2.T2PK = T3.T2PK
RIGHT JOIN Table1
ON T1.T1PK = T2.T1PK
-
This select statement will return all employees including those with no assigned projects:
Select emp.EmployeeName, proj.ProjectName, emp.ManagerName, proj.StartDate, proj.EndDate
FROM Employees emp
LEFT JOIN Projects proj
ON emp.EmployeeID = proj.EmployeeID
This select statement will return all projects including those with no assigned employees:
Select emp.EmployeeName, proj.ProjectName, emp.ManagerName, proj.StartDate, proj.EndDate
FROM Employees emp
RIGHT JOIN Projects proj
ON emp.EmployeeID = proj.EmployeeID
The only change between the two queries is that the first is a Left Outer Join, and the second is a Right Outer Join.
-
You have inner join, left join and full outer join anyway. A right join is a small addition that makes the set of possibilities symmetrical and easy to remember.
Furthermore, if more than two tables or subqueries are involved (as already mentioned by Ken above), an SQL statement can become more complicated if a left or right join is not possible. Having both directions helps you to keep tables in a "logical" order.
-
RIGHT joins is only there for us mere mortals. For SQL Server, there is only LEFT joins.
Try these two:
select *
from sysobjects so
left join syscolumns sc
on so.id = sc.id
select *
from syscolumns sc
right join sysobjects so
on so.id = sc.id
.. you'll find that the plans are identical, and the right join is converted to a left join when executed.
/Kenneth
-
In all my DB work, I don't think I've ever written a Right join. At least not a keeper.
-
If you use Enterprise Manager to join many tables with outer joins, it will frequently produce both LEFT and RIGHT outer joins. It will also, on occasion, produce joins that have ONs clustered as a group.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
-- EDIT -- sorry, I posted to the wrong question and removed it
--______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply