Home

 › 

Articles

 › 

What Are Joins in SQL (Inner, Left, Right, and Full) – With Examples

joins in sql

What Are Joins in SQL (Inner, Left, Right, and Full) – With Examples

SQL JOIN operations are a powerful tool for retrieving data from multiple tables in a relational database. Whether you’re an experienced data analyst or a programmer or just someone who’s starting out with databases, it’s important to understand the basics of JOIN operations: what they are and how to use them.

In this article, we’ll cover the different types of JOIN operations in SQL – INNER, LEFT, RIGHT, and FULL – as well as provide examples of each type using sample SQL tables. Let’s get started! 

What Exactly Are Joins in SQL?

At its core, an SQL JOIN operation combines data from two or more tables into a single result set. This operation is used when you need to retrieve information from multiple tables that have some kind of relationship by means of a related column between them. The related column is called a join condition. 

The resulting table contains all the columns from the combined tables, with rows matched according to the join condition. For example, if you had two tables containing customer orders and order items, respectively, you could use a JOIN operation to combine the data into one table that shows all the orders and their associated items. 

Types of Joins in SQL

As mentioned earlier, there are four main types of joins in SQL: 

  • INNER JOIN, 
  • LEFT JOIN (or LEFT OUTER JOIN), 
  • RIGHT JOIN or (RIGHT OUTER JOIN) and, 
  • FULL JOIN (or FULL OUTER JOIN).  

Each type operates differently depending on what kind of relationship exists between the tables being joined. Let’s look at each and every one of them in detail.

INNER JOIN

Virtualization software digital technology computer PC
SQL introduced the concept of accessing many records with one single command, without needing to specify how to reach them.

An INNER JOIN is the most commonly used type of join in SQL. It’s sometimes referred to as a “simple join,” and using JOIN instead of INNER JOIN in a query is syntactically correct. Simple joins are used to combine rows from two or more tables based on a common column.

The INNER JOIN operation returns only the rows where there is a match between the related columns in both tables. This means that if there is no match in one of the tables, that row is not returned. 

To illustrate this concept further, let’s consider two SQL tables: Customers and Orders.

Customers Table

CustomerIDCustomerNameContactName
1JohnJohn Smith
2MariaMaria Garcia
3PeterPeter Johnson

Orders Table

OrderIDCustomerIDOrderDate
112022-03-15
222022-04-10
312022-04-20


We can use INNER JOIN to get the orders made by each customer, like so:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers

ON Orders.CustomerID = Customers.CustomerID;

In the sample query above, we’ve used INNER JOIN to combine the Orders and Customers tables based on the CustomerID column. 

The query result will, therefore, be:

OrderIDCustomerNameOrderDate
1John2022-03-15
2Maria2022-04-10
3John2022-04-20

LEFT JOIN

A LEFT JOIN (or LEFT OUTER JOIN) is used to combine the rows from two or more tables based on a common column, but it also returns all rows from the left table (or the first table in the join).

A LEFT JOIN operation, therefore, returns all the rows from the left table and only the matching rows from the right table. If there is no matching record in the right table, the result will contain NULL values in the columns from the right table. 

Using the tables from the previous example, we can use the LEFT JOIN to combine the Customers and Orders tables (based on the CustomerID) to get orders made by all customers, even if there are no orders for a particular customer like so: 

SELECT Customers.CustomerName, Orders.OrderDate

FROM Customers

LEFT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

The result of the query will be:

CustomerNameOrderDate
John2022-03-15
Maria2022-04-10
John2022-04-20
PeterNULL

As expected, since Peter has no orders, the OrderDate column for Peter contains NULL.

RIGHT JOIN

OpenCL vs CUDA
SQL was originally based on relational algebra and tuple relational calculus.

A RIGHT JOIN (or RIGHT OUTER JOIN) works in the same way as a LEFT JOIN, except that it returns all rows from the right table (or the second table in the join). In other words, a RIGHT JOIN operation returns all the rows from the right table and only the matching rows from the left table.

If there is no matching row in the left table, the result will contain NULL values in the columns from the left table. Using the previous example, we can use RIGHT JOIN to get all orders placed by the customers, like this:

SELECT Customers.CustomerName, Orders.OrderDate

FROM Customers

RIGHT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

The query above will give us the following result:

CustomerNameOrderDate
John2022-03-15
Maria2022-04-10
John2022-04-20

As we expected, Peter is not included in the query result because he hasn’t made any orders yet, whereas John has two.

FULL JOIN

A FULL JOIN operation returns all the rows from both tables, including the unmatched rows. If there is no match in one of the tables, the result will contain NULL values in the columns from the table with no match.

A FULL JOIN, thus, combines the result sets of both a LEFT JOIN and a RIGHT JOIN. It returns all rows from both tables, even if there is no matching row in the other table. 

Using the same tables as before, we can use a FULL JOIN to get all orders and customers, even if there are no orders for a particular customer or if there are no customers who have made orders yet, like so:

SELECT Customers.CustomerName, Orders.OrderDate

FROM Customers

FULL JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

This gives us the following result:

CustomerNameOrderDate
John2022-03-15
Maria2022-04-10
John2022-04-20
PeterNULL

Peter is now included in the query result, even though he hasn’t made any orders yet, and the OrderDate column for Peter contains NULL.

FULL OUTER JOIN is equivalent to FULL JOIN, and you can use them interchangeably.

Are There Alternatives to the Common Joins in SQL?

While join operations are an essential tool for combining data from multiple tables in SQL, there are alternative methods that can achieve similar results. These methods include subqueries, UNION, and UNION ALL statements. Let’s briefly explore each of these alternatives to see how they compare to the use of JOINS.

SQL Subqueries

Subqueries are nested queries that can be used to retrieve data from one or more tables. They can be used to filter, sort, or aggregate data, and can be nested within other queries. One common use case for subqueries is to retrieve data from one table based on data from another table.

For example, instead of using a join to combine data from the Orders and Customers tables, you can use a subquery to retrieve data from the Customers table based on data from the Orders table:

SELECT CustomerName, City

FROM Customers

WHERE CustomerID IN (SELECT CustomerID FROM Orders);

This query will return all customers who have placed an order, along with their city.

UNION statement

The UNION statement is used to combine the results of two or more SELECT statements into a single result set. It is similar to a join operation in that it combines data from multiple tables, but it does not require a common column between the tables.

For example, you can use UNION to combine data from the Customers and Orders tables that have the same columns:

SELECT CustomerName, ContactName, City FROM Customers

UNION

SELECT CustomerName, ContactName, City FROM Orders;

The query above will return a result set that includes all customers and orders, with their names and cities.

UNION ALL statement

The UNION ALL statement is similar to the UNION statement, but it does not remove duplicates from the result set. This means that it will include all rows from both tables, even if there are duplicates.

For example,

SELECT CustomerName, City FROM Customers

UNION ALL

SELECT CustomerName, ShipCity FROM Orders;

This query will give us a result set that includes all customers and orders, with their names and cities, even if there are duplicates.

What Would Be the Benefit of Using Joins in SQL Over Other Alternatives?

While the above alternatives can be used to combine data from multiple tables and give us the expected results, joins are the most efficient way to do it. One key advantage of join operations is that they are generally more efficient than subqueries and UNION operations.

Join operations allow you to retrieve data from multiple tables in a single query, which can be more efficient than running multiple queries or nested subqueries to achieve the same result. 

Joins are also a more intuitive way to combine data from multiple tables, as they allow you to specify exactly how the data should be joined based on the relationship between the tables. This can make it easier to understand and modify queries over time, as the join conditions clearly indicate how the data is being combined.

Of course, there may be situations where subqueries or UNION operations are more appropriate than join operations, depending on the specific requirements of the query. In general, though, joins offer a simpler, more powerful, and efficient way to combine data from multiple tables in SQL compared to other methods.

Wrapping Up

SQL joins are critical to querying and manipulating data stored in databases. They are quite helpful in the context of relational databases as they allow us to create complex data queries that would otherwise be impossible.

Each join operation has its use case, and you should choose the appropriate one based on the data you are working with and the type of result you want to obtain. By mastering the four types of joins in SQL — INNER, LEFT, RIGHT, and FULL — you can work with databases efficiently and extract meaningful insight from your data.

Frequently Asked Questions

What’s the significance of JOINS in SQL queries?

Join operations in SQL are used to combine data from two or more tables into a single result set. This operation is used when you need to retrieve information from multiple tables that have some kind of relationship by means of a related column between them.

What is the difference between a LEFT JOIN and a RIGHT JOIN?

A LEFT JOIN (or LEFT OUTER JOIN) combines the rows from two or more tables based on a common column, but it also returns all rows from the left table. A RIGHT JOIN (or RIGHT OUTER JOIN) is the same as a LEFT JOIN, except that it returns all rows from the right table.

What is the purpose of a FULL JOIN in SQL?

A FULL JOIN operation returns all the rows from both tables, including the unmatched rows. If there is no match in one of the tables, the result will contain NULL values in the columns from the table with no match.

What are some best practices when using JOIN operations in SQL?

Some best practices when using joins in SQL include using the right join type based on the data and the type of result you want to obtain, using aliases to make your query more readable, and/or using the WHERE clause to refine the query result.

Can you use more than two tables in an SQL JOIN operation?

Yes, you can use more than two tables in an SQL join operation. You can join multiple tables by specifying the join conditions in the ON clause of the query. The ON clause can contain multiple join conditions, separated by AND or OR operators.

To top