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 end 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

__________________
cheers,
debsoft

It has to do with the types of records that are returned.

For example, an Inner Join returns data ONLY when rows exist in both tables, so in your above example if I did an INNER Join on emp.EmployeeID = proj.EmployeeID an ID must exist in both the source table and the table we are joining on.

A LEFT JOIN will return data even if there is no matching data in the second table. So in your example, if the Employees table contained the ID 1 and the Projects table did not have an employee_fk of 1 all of the values you are returning from the Projects table will return NULL.

A RIGHT JOIN is the exact opposite. If there is data in the second table (the table you are joining) that does not exist in your first table (the table in your FROM clause) the values from your first table will return NULL.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

USE THIS ...

Select emp.EmployeeName, proj.ProjectName, emp.ManagerName, proj.StartDate, proj.EndDate
FROM Employees emp
        INNER JOIN Projects proj ON emp.EmployeeID = proj.EmployeeID



With Regards,
Muralidharan.D