SQL Joins with Examples

Have used the following 2 tables Employee and Department as examples.

Employee Table :- Department Table:-

EmployeeID EmployeeName DepartmentID DepartmentID DepartmentName
1 Smith 1 1 HR
2 Jack 2 2 Finance
3 Jones 2 3 Security
4 Andrews 3 4 Sports
5 Dave 5 5 HouseKeeping
6 Jospeh 6 Electrical
************************************************************************************************
Inner Join

An Inner Join will take two tables and join them together based on the values in common columns ( linking field ) from each table.

Example 1 :- To retrieve only the information about those employees who are assinged to a department.

Select Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName From Employee Inner Join Department on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

EmployeeID EmployeeName DepartmentName
1 Smith HR
2 Jack Finance
3 Jones Finance
4 Andrews Security
5 Dave HouseKeeping

Example 2:- Retrieve only the information about departments to which atleast one employee is assigned.

Select Department.DepartmentID,Department.DepartmentName From Department Inner Join Employee on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

DepartmentID DepartmentName
1 HR
2 Finance
3 Security
5 HouseKeeping
************************************************************************************************

Outer Joins :-

Outer joins can be a left, a right, or full outer join.

Left outer join selects all the rows from the left table specified in the LEFT OUTER JOIN clause, not just the ones in which the joined columns match.

Example 1:- To retrieve the information of all the employees along with their Department Name if they are assigned to any department.

Select Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName From Employee LEFT OUTER JOIN Department on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

EmployeeID EmployeeName DepartmentName
1 Smith HR
2 Jack Finance
3 Jones Finance
4 Andrews Security
5 Dave HouseKeeping
6 Jospeh

Right outer join selects all the rows from the right table specified in the RIGHT OUTER JOIN clause, not just the ones in which the joined columns match.

Example 2:- use Right Outer join to retrieve the information of all the departments along with the detail of EmployeeName belonging to each Department, if any is available.

Select Department.DepartmentID,Department.DepartmentName,Employee.EmployeeName From Employee Outer Join Department on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

DepartmentID DepartmentName EmployeeName
1 HR Smith
2 Finance Jack
2 Finance Jones
3 Security Andrews
4 Sports NULL
5 HouseKeeping Dave
6 Electrical NULL

This query will result in Null value for Employee Name where no Employee is assigned to that department.