What is the difference between INNER JOIN and LEFT JOIN?
SQL JOIN clauses are used to combine rows from two or more tables, based on a related column between them. This document will focus on explaining the fundamental differences and use cases for INNER JOIN and LEFT JOIN.
SQL JOINs Overview
JOINs are a core concept in relational databases, enabling you to retrieve data from multiple tables simultaneously. They establish a relationship between tables based on common columns, typically primary and foreign keys. For our examples, consider two tables: 'Customers' and 'Orders'.
| CustomerID | Name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| OrderID | CustomerID | Amount |
|---|---|---|
| 101 | 1 | 150.00 |
| 102 | 2 | 25.00 |
| 103 | 1 | 75.00 |
| 104 | 4 | 50.00 |
INNER JOIN
An INNER JOIN returns only the rows that have matching values in both tables. If a record in one table does not have a matching record in the other table, it is excluded from the result set. It's the most common type of JOIN and is often implied if you simply use the JOIN keyword without specifying a type.
SELECT C.CustomerID, C.Name, O.OrderID, O.Amount
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID;Result of the INNER JOIN on 'Customers' and 'Orders':
| CustomerID | Name | OrderID | Amount |
|---|---|---|---|
| 1 | Alice | 101 | 150.00 |
| 1 | Alice | 103 | 75.00 |
| 2 | Bob | 102 | 25.00 |
LEFT JOIN (or LEFT OUTER JOIN)
A LEFT JOIN (also known as LEFT OUTER JOIN) returns all rows from the left table, and the matching rows from the right table. If there is no match for a row in the left table, the columns from the right table will contain NULLs in the result set. It preserves all records from the 'left' table (the first table mentioned in the FROM clause).
SELECT C.CustomerID, C.Name, O.OrderID, O.Amount
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID;Result of the LEFT JOIN on 'Customers' and 'Orders':
| CustomerID | Name | OrderID | Amount |
|---|---|---|---|
| 1 | Alice | 101 | 150.00 |
| 1 | Alice | 103 | 75.00 |
| 2 | Bob | 102 | 25.00 |
| 3 | Charlie | NULL | NULL |
Key Differences Summarized
- Matching Rows: INNER JOIN returns only rows where a match exists in both tables. LEFT JOIN returns all rows from the left table, and matched rows from the right table.
- Unmatched Rows: INNER JOIN excludes unmatched rows from either table. LEFT JOIN includes all rows from the left table, padding columns from the right table with 'NULL's where no match exists.
- Result Size: The result of an INNER JOIN can be smaller than or equal to the smallest of the two tables. The result of a LEFT JOIN will always have at least as many rows as the left table.
When to Use Which?
Use INNER JOIN when:
- You only care about records that have a direct relationship in both tables.
- You want to find all customers who have placed at least one order.
- You need to combine information only where there's a common data point across both datasets.
Use LEFT JOIN when:
- You want to retrieve all records from one table (the 'left' table) regardless of whether they have a match in the second table.
- You want to find all customers, and if they have orders, include order details (otherwise, show 'NULL's for order details).
- You need to identify records in the left table that *do not* have a match in the right table (often achieved by adding
WHERE right_table.id IS NULL).
Conclusion
Choosing between INNER JOIN and LEFT JOIN depends entirely on the specific data you need to retrieve. INNER JOIN is for intersecting data, while LEFT JOIN is for preserving all data from one table while optionally adding related data from another. Understanding their distinct behaviors is crucial for writing accurate and efficient SQL queries.