I’m not always working with SQL Server, and sometimes keep forgetting which SQL join to use, this article keeps me refreshed and hopefully give you an overview of the three main join types: Inner, Left and Right.

Inner Join

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.

Example

Who has ordered a product, and what did they order?

SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

Result

Name	Product
Hansen, Ola	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Left Join

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

Example

List all employees, and their orders - if any.

SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

Result

Name	Product
Hansen, Ola	Printer
Svendson, Tove
Svendson, Stephen	Table
Svendson, Stephen	Chair
Pettersen, Kari

Right Join

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees).

Example

List all employees, and their orders - if any

SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

Result

Name	Product
Hansen, Ola	Printer
	Monitor
Svendson, Stephen	Table
Svendson, Stephen	Chair