Sunday, February 15, 2009

LEFT JOIN in Oracle and SQL Server

You have two tables:
tblEmployee(EmployeeID, EmployeeName, DepartmentID, ...) contains all employee information
tblDepartment(DepartmentID, DepartmentName) contains all departments in your company
Each employee belongs to one department. Sometimes, new employees don't have related department. You want to display all employees with their related departments. If someone does not have any department, display null
In this case, you have to use LEFT JOIN to query your data
In SQL Server:
SELECT t1.EmployeeID, t1.EmployeeName, t2.DepartmentName
FROM tblEmployee t1 LEFT JOIN tblDepartment t2 ON t1.DepartmentID = t2.DepartmentID

In Oracle:
SELECT t1.EmployeeID, t1.EmployeeName, t2.DepartmentName
FROM tblEmployee t1, tblDepartment t2
WHERE t1.DepartmentID = t2.DepartmentID(+)

No comments:

Post a Comment