Understand the Basics of SQL Joins with Examples: Inner Join, Left Join, Right Join, and Outer Join
SQL joins are used to combine data from two or more tables in a relational database. The type of join determines the result set returned from the join operation. In this article, we will explain the differences between the four most commonly used join types: left join, right join, inner join, and outer join.
Inner Join
An inner join returns only the rows from both tables that match the join condition. In other words, it only returns the rows that have matching values in both tables. The result set includes only the rows where there is a match in both tables. For example, consider two tables, "Customers" and "Orders", where the join condition is based on the customer ID. An inner join between these two tables would return only the rows where there is a match in both tables, i.e., the customers who have placed an order.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Left Join (Left Outer Join):
A left join returns all the rows from the left table (table1), and the matching rows from the right table (table2). If there is no match in the right table, NULL values will be returned for the columns from the right table. For example, consider the same two tables “Customers” and “Orders.” A left join between these two tables would return all the customers, and their corresponding orders if available.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Right Join (Right Outer Join):
A right join is similar to a left join, but the order of the tables is reversed. A right join returns all the rows from the right table (table2), and the matching rows from the left table (table1). If there is no match in the left table, NULL values will be returned for the columns from the left table. For example, consider the same two tables “Customers” and “Orders.” A right join between these two tables would return all the orders, and their corresponding customers if available.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Outer Join:
An outer join returns all the rows from both tables, including the unmatched rows from either table. An outer join can be either a left outer join or a right outer join. For example, consider the same two tables “Customers” and “Orders.” An outer join between these two tables would return all the customers and all the orders, with NULL values for the unmatched rows.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Where to next
- check our SQL cheatsheet
- explore SQL in our interactive playground
