Performing Left Joins in SQL
SQL joins are one of the fundamental concepts used in relational databases to retrieve data from multiple tables based on logical relationships between them. In this chapter, we will focus on the LEFT JOIN (also known as LEFT OUTER JOIN). This join is useful when you want to retrieve all records from one table, and the matched records from another table, filling in NULLs for non-matching records.
By the end of this chapter, you will have a comprehensive understanding of how LEFT JOINS work, when to use them, and how to build complex queries using LEFT JOIN.
What is a LEFT JOIN?
A LEFT JOIN is a type of join that retrieves all rows from the left table (the first table listed in the query), along with the matched rows from the right table (the second table). If there is no match, the result will still include all rows from the left table, but the columns from the right table will contain NULL values.
This is useful when you want to retrieve all records from one table, regardless of whether there is a match in the other table.
Syntax of LEFT JOIN:
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
In this syntax:
- table1 is the left table (from which all rows will be returned).
- table2 is the right table (from which only matched rows will be returned).
- The
ONclause specifies the column on which the join is based.
Basic Example of LEFT JOIN
Let’s start by using two tables: employees and departments.
employees Table:
| employee_id | employee_name | department_id |
|---|---|---|
| 1 | Alice | 101 |
| 2 | Bob | 102 |
| 3 | Charlie | NULL |
| 4 | David | 103 |
departments Table:
| department_id | department_name |
|---|---|
| 101 | HR |
| 102 | IT |
| 104 | Marketing |
Query:
SELECT employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
Output:
| employee_name | department_name |
|---|---|
| Alice | HR |
| Bob | IT |
| Charlie | NULL |
| David | NULL |
Explanation:
- The LEFT JOIN ensures that all employees are returned, even if they are not assigned to any department.
- For employees like Charlie and David who don’t have a matching department (or have a NULL value in
department_id), thedepartment_nameis set toNULL. - All rows from the
employeestable (left table) are included in the result set, even if there is no matching department in thedepartmentstable.
Using LEFT JOIN to Handle Missing Data
One of the key benefits of using LEFT JOIN is that it allows you to deal with missing or incomplete data. In real-world databases, it is common to have records in one table without corresponding records in related tables. A LEFT JOIN helps include those records in the result set.
Example: Orders and Customers
Imagine you have two tables: customers and orders.
customers Table:
| customer_id | customer_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
orders Table:
| order_id | customer_id | order_total |
|---|---|---|
| 101 | 1 | 250 |
| 102 | 2 | 150 |
Query:
SELECT customers.customer_name, orders.order_total
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
Output:
| customer_name | order_total |
|---|---|
| Alice | 250 |
| Bob | 150 |
| Charlie | NULL |
Explanation:
- The query retrieves all customers and their corresponding orders.
- For customers like Charlie, who has not placed any orders, the
order_totalisNULLbecause there is no matching entry in theorderstable. - All rows from the
customerstable are returned, regardless of whether they have matching records in theorderstable.
LEFT JOIN with Multiple Tables
You can perform LEFT JOINS with more than two tables to combine data from multiple sources. Let’s introduce a third table, products, and join it with the orders and customers tables.
products Table:
| product_id | product_name | price |
|---|---|---|
| 201 | Laptop | 1000 |
| 202 | Phone | 500 |
order_items Table:
| order_id | product_id | quantity |
|---|---|---|
| 101 | 201 | 1 |
| 102 | 202 | 2 |
Query:
SELECT customers.customer_name, orders.order_id, products.product_name, order_items.quantity
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN order_items ON orders.order_id = order_items.order_id
LEFT JOIN products ON order_items.product_id = products.product_id;
Output:
| customer_name | order_id | product_name | quantity |
|---|---|---|---|
| Alice | 101 | Laptop | 1 |
| Bob | 102 | Phone | 2 |
| Charlie | NULL | NULL | NULL |
Explanation:
- The query uses multiple LEFT JOINS to retrieve data from
customers,orders,order_items, andproducts. - Customers who have not placed any orders (like Charlie) still appear in the result, with
NULLvalues in the columns related to orders, products, and quantities.
Filtering with LEFT JOIN
When using LEFT JOIN, you can apply filters to limit the result set based on certain conditions. However, it’s essential to understand how filtering works with LEFT JOINs, especially when dealing with NULL values.
Example: Filtering Orders with LEFT JOIN
Let’s say we want to list all customers, but only show orders that have a total value greater than 200.
Query:
SELECT customers.customer_name, orders.order_total
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_total > 200;
Output:
| customer_name | order_total |
|---|---|
| Alice | 250 |
Explanation:
- This query lists all customers, but filters to show only orders with an
order_totalgreater than 200. - Charlie is excluded because he hasn’t placed any orders, and Bob’s order is excluded because it doesn’t meet the condition (
order_total = 150).
Important Note: Preserving Non-Matching Records
To include customers with no orders, use a different approach to filter the data:
Query:
SELECT customers.customer_name, orders.order_total
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_total > 200 OR orders.order_total IS NULL;
Output:
| customer_name | order_total |
|---|---|
| Alice | 250 |
| Charlie | NULL |
Explanation:
- By adding
OR orders.order_total IS NULL, you ensure that customers without any orders (like Charlie) are still included in the result.
LEFT JOIN vs INNER JOIN
Understanding the difference between a LEFT JOIN and an INNER JOIN is crucial:
- INNER JOIN only returns rows where there is a match between the two tables.
- LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, the result will contain
NULLvalues for columns from the right table.
Comparison Example:
Let’s revisit our earlier customers and orders tables:
-- INNER JOIN Example
SELECT customers.customer_name, orders.order_total
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
Output:
| customer_name | order_total |
|---|---|
| Alice | 250 |
| Bob | 150 |
-- LEFT JOIN Example
SELECT customers.customer_name, orders.order_total
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
Output:
| customer_name | order_total |
|---|---|
| Alice | 250 |
| Bob | 150 |
| Charlie | NULL |
- In the INNER JOIN example, Charlie is excluded because he doesn’t have any matching orders.
- In the LEFT JOIN example, Charlie is included, but his
order_totalisNULLsince he hasn’t placed any orders.
Common Mistakes with LEFT JOIN
Here are some common mistakes to avoid when working with LEFT JOIN:
Misunderstanding NULL Handling
When performing a LEFT JOIN, unmatched rows from the right table will have NULL values. Ensure you handle these NULL values correctly, especially in conditional statements.
Example:
SELECT customers.customer_name, orders.order_total
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_total > 200;
This query would exclude customers without orders because their order_total is NULL, which doesn’t satisfy the condition > 200. Use an OR clause to include them if needed:
WHERE orders.order_total > 200 OR orders.order_total IS NULL;
LEFT JOIN is a powerful tool in SQL that allows you to retrieve all records from one table and the matched records from another. It is essential when you want to include all rows from the left table, even if there are no corresponding records in the right table. You can use LEFT JOIN to handle missing data, perform multiple joins, and apply filters intelligently. Happy coding !❤️