SQL Join (Inner, Left, Right, Full) with Example

In the earlier tutorial, we have seen all the basic SQL commands. In this tutorial, we are going to learn SQL join operations for combining multiple tables.

The join clause is used to combine the multiple rows from two or more tables.

The join clause is very much useful in a relational database as you will come across situations where you need to combine rows from multiple tables to extract useful data.

There are four different ways we can combine the rows, so the four different types of SQL join operations.

It is easy to remember if you visualize it using this simple Venn diagram.

Many times, SQL interview questions are asked about the types of join operations in SQL. Like,

• What is the difference between INNER and OUTER Join?
• You will be given a situation and expected result, and you will be asked which type of join you will use.
• What are the minimum and maximum rows you will get after performing join operations?

Going through this tutorial, you will get answers to all these questions.

Let’s see each of the SQL join operations with examples one by one.

To illustrate all the join operations, we are considering the following tables.

Orders: detail about the orders.
(OrderID, CustomerID, EmployeeID)

Customers: detail about the customers
(CustomerID, CustomerName)

Employees: detail about the employees
(EmployeeID, EmployeeName)

INNER JOIN

It returns all the matching rows from targeted tables.

SQL Query Example:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers 
ON Orders.CustomerID = Customers.CustomerID;

If there is no matching row, you will get the empty table as a query result.

The maximum number of rows in the result query will be less than the number of rows in the smaller table rows.

FULL Outer JOIN

It returns all the rows (matching and non-matching rows) from targeted tables.

SQL Query Example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders 
ON Customers.CustomerID=Orders.CustomerID

For the row in one table, if there is no matching row in another table, fields from the later table will be shown as null.

If the Customer table has n rows and the Order table has m rows, the maximum number of rows in the table can be m*n.

LEFT Outer JOIN

It returns all the rows from the left table and matching rows from the right table.

SQL Query Example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;

If there is any customer who has not ordered any product, OrderID will be shown as null.

RIGHT Outer JOIN

It returns all the rows from the right table and matching rows from the left table.

SQL Query Example:

SELECT Orders.OrderID, Employees.Name
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID;

If there is any employee who has not ordered any product, OrderID will be shown as null.

Some important points to note down:

• You can use the ORDER BY clause to sort the rows.
• For any join operation for “table1” (with m rows) and “table2” (with n rows), the number of rows in the result query can not exceed m*n.
• You can also use different filters for SELECT queries to filter out rows based on the conditions.

I hope you find this SQL join tutorial simple and easy to understand. If you have any doubt, let’s discuss in the comment section below.

Aniruddha Chaudhari

I am complete Python Nut, love Linux and vim as an editor. I hold a Master of Computer Science from NIT Trichy. I dabble in C/C++, Java too. I keep sharing my coding knowledge and my own experience on <b>CSEstack.org</b> portal.