SQL Joins

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

assuming we have a sample data:

Example Data

Employees Table

Departments Table

INNER JOIN

Returns records that have matching values in both tables.

From this #Example Data, if we want to find out at least one match in both tables:

SELECT Employees.EmployeeName, Departments.DepartmentName 
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID

Expected Result:

LEFT OUTER JOIN

Returns all records from the left table and the matched records from the right table.

From this #Example Data, if we want to find out all employees and matched rows from the department table.

SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
LEFT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID

Expected result:

RIGHT OUTER JOIN

Returns all records from the right table and the matched records from the left table.

From this #Example Data, if we want to find out all departments and matched rows from the employee table.

SELECT Employees.EmployeeName, Departments.DepartmentName 
FROM Employees
RIGHT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID

Expected result:

FULL OUTER JOIN

Returns all records when there is a match in either left or right table.

From this #Example Data, this join returns rows when there is a match in one of the tables. Thus, it shows all records from both tables, with NULL on one side if there is no match.

SELECT Employees.EmployeeName, Departments.DepartmentName 
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID

Expected result: