Chapter 15: Understanding LEFT, RIGHT, and FULL JOINs (Outer Joins)

While INNER JOIN is fantastic for finding matching records, there are many scenarios where you need to retrieve data from one table even if there’s no corresponding match in the other table. For example:

  • Listing all users, and for those who have placed orders, showing their order details (but still listing users without orders).
  • Showing all products, and if they’ve been ordered, showing their order quantities (but still listing products that have never been ordered).

This is where Outer JOINs come into play. They include not only the rows that match in both tables but also the non-matching rows from one or both tables. The three main types of Outer JOINs are LEFT JOIN, RIGHT JOIN, and FULL JOIN (though FULL JOIN is not directly supported in MySQL but can be simulated).

What are Outer Joins?

Outer JOINs return rows from at least one of the tables, even if no matching rows are found in the other table. The non-matching columns from the unmatched side will have NULL values in the result set.

  • LEFT JOIN (or LEFT OUTER JOIN):
    • Returns all rows from the left table (the first table mentioned in the FROM clause).
    • Returns matching rows from the right table.
    • If there’s no match in the right table, the columns from the right table will have NULL values.
    • Think: “Give me everything from the left, and matching stuff from the right.”
  • RIGHT JOIN (or RIGHT OUTER JOIN):
    • Returns all rows from the right table (the second table mentioned in the FROM or JOIN clause).
    • Returns matching rows from the left table.
    • If there’s no match in the left table, the columns from the left table will have NULL values.
    • Think: “Give me everything from the right, and matching stuff from the left.”
    • Note: RIGHT JOIN can always be rewritten as a LEFT JOIN by simply swapping the table order. A RIGHT JOIN B is the same as B LEFT JOIN A. For consistency and readability, most developers prefer to use LEFT JOIN exclusively.
  • FULL JOIN (or FULL OUTER JOIN):
    • Returns all rows when there is a match in either the left or the right table.
    • If a row in the left table has no match in the right, the right-side columns will be NULL.
    • If a row in the right table has no match in the left, the left-side columns will be NULL.
    • Think: “Give me everything from both tables, showing NULLs where there’s no match.”
    • Important for MySQL/MariaDB: FULL JOIN is not directly supported. You typically achieve the same result by combining a LEFT JOIN and a RIGHT JOIN with a UNION clause.

SQL Syntax & Examples (MySQL/MariaDB Focus)

Let’s continue using our scriptbuzz_db. Ensure your users, products, orders, and order_items tables are populated with some data, including users without orders, and products not in orders, to observe the NULL behavior.

If jane_doe (user_id 3) has no orders, and maybe you have a product not in order_items, those will be good for demonstrating NULLs.

1. LEFT JOIN Examples

Syntax:

SQL
SELECT columns
FROM left_table
LEFT JOIN right_table
ON left_table.common_column = right_table.common_column;

Example 1: List all users and their order IDs (if they have any)

SQL
USE scriptbuzz_db;

SELECT
    u.user_id,
    u.username,
    o.order_id,
    o.total_amount
FROM users AS u
LEFT JOIN orders AS o
ON u.user_id = o.user_id
ORDER BY u.user_id, o.order_id;

Expected Output (example, assuming user 3 has no orders):

user_idusernameorder_idtotal_amount
1john_doe1150.75
1john_doe2200.00
1john_doe430.00
2admin_user350.25
2admin_user5100.00
3jane_doeNULLNULL

What it does: All users are listed (users is the left table). For users who have corresponding entries in the orders table, their order_id and total_amount are displayed. For jane_doe (user_id 3) who has no orders, the order_id and total_amount columns from the orders table show NULL, but jane_doe herself is still included.

Example 2: List all products and count how many times they appear in order items

SQL
SELECT
    p.product_id,
    p.name,
    COUNT(oi.product_id) AS times_ordered
FROM products AS p
LEFT JOIN order_items AS oi
ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
ORDER BY p.name;

Expected Output (example – assuming Laptop Pro is in 2 orders, Wireless Mouse in 2, Mech Keyboard in 2, others not in any order_items):

product_idnametimes_ordered
7Gaming Monitor0
8Gaming Mouse0
1Laptop Pro2
3Mechanical Keyboard2
4USB-C Hub0
6USB Keyboard0
5Webcam 1080p0
2Wireless Mouse2

What it does: All products are listed. If a product has appeared in order_items, it counts how many times. If a product has never appeared in order_items, COUNT(oi.product_id) correctly returns 0 because COUNT() ignores NULLs. This is a common pattern for “products never ordered” or similar reports.

2. RIGHT JOIN Examples

Syntax:

SQL
SELECT columns
FROM left_table
RIGHT JOIN right_table
ON left_table.common_column = right_table.common_column;

As mentioned, RIGHT JOIN is just the inverse of LEFT JOIN.

Example 3: List all orders and the username of the customer (even if a theoretical order had no matching user)

SQL
-- This query is just for demonstration. In a properly constrained database,
-- an order *must* have a user, so the right table (orders) would always
-- have a match in the left table (users).
SELECT
    u.username,
    o.order_id,
    o.total_amount
FROM users AS u
RIGHT JOIN orders AS o
ON u.user_id = o.user_id
ORDER BY o.order_id;

What it does: Returns all rows from orders (the right table) and matching rows from users. If there were an order with a user_id that didn’t exist in the users table, that order would still be listed, and u.username would be NULL. Given our foreign key, this scenario won’t occur with valid data.

Equivalent LEFT JOIN:

SQL
SELECT
    u.username,
    o.order_id,
    o.total_amount
FROM orders AS o  -- Orders is now the LEFT table
LEFT JOIN users AS u -- Users is now the RIGHT table
ON o.user_id = u.user_id
ORDER BY o.order_id;

Both queries yield the same result, but the LEFT JOIN version is generally preferred for consistency.

3. Simulating FULL JOIN in MySQL/MariaDB

Since FULL JOIN isn’t directly available, you combine a LEFT JOIN and a RIGHT JOIN (or two LEFT JOINs by swapping tables) using the UNION operator. UNION combines the result sets of two or more SELECT statements, removing duplicate rows. UNION ALL includes duplicates.

Scenario: We want to list all users, all products, and see if they’re linked in order_items (as user_id is linked to orders, and orders linked to order_items, and order_items linked to products). This is a complex FULL JOIN for demonstration.

Let’s simplify to a FULL JOIN between users and orders to show users without orders and orders without users (if any exist).

SQL
-- Part 1: LEFT JOIN (all users, matching orders)
SELECT
    u.username,
    o.order_id,
    o.total_amount
FROM users AS u
LEFT JOIN orders AS o ON u.user_id = o.user_id

UNION -- UNION removes duplicate rows

-- Part 2: RIGHT JOIN (all orders, matching users, but primarily to catch orders without a user)
-- Note: Given the FK, orders should always have a user.
-- This part primarily catches cases where users.user_id might be NULL and orders.user_id is not.
-- Or if an order references a non-existent user (which FKs prevent).
SELECT
    u.username,
    o.order_id,
    o.total_amount
FROM users AS u
RIGHT JOIN orders AS o ON u.user_id = o.user_id
WHERE u.user_id IS NULL; -- This WHERE clause is crucial to select only unmatched RIGHT rows
                         -- if you use UNION, but with UNION ALL you'd exclude this.
                         -- For a true FULL JOIN, the WHERE IS NULL is applied AFTER UNION.
                         -- The canonical way is (LEFT JOIN) UNION (RIGHT JOIN WHERE IS NULL on LEFT side)

Corrected Canonical FULL OUTER JOIN Simulation in MySQL:

SQL
-- Get all users and their orders
SELECT
    u.username,
    o.order_id,
    o.total_amount
FROM users AS u
LEFT JOIN orders AS o ON u.user_id = o.user_id

UNION ALL -- Use UNION ALL to include all rows from both parts before filtering, then filter if necessary

-- Get all orders that don't have a matching user (if any, due to data integrity issues or lack of FK)
SELECT
    u.username, -- Will be NULL if no user match
    o.order_id,
    o.total_amount
FROM users AS u
RIGHT JOIN orders AS o ON u.user_id = o.user_id
WHERE u.user_id IS NULL; -- Only include rows from the right join where the left side didn't match
                         -- (i.e., orders that don't have a user)

Explanation of the FULL JOIN simulation:

  1. The LEFT JOIN part gets all users and their matched orders. Any user without an order will have NULLs for order details.
  2. The RIGHT JOIN part (with WHERE u.user_id IS NULL) is specifically designed to catch any orders that don’t have a matching user. Given a foreign key, this part should return no rows in a healthy database. If it did return rows, it would indicate data integrity issues (orders pointing to non-existent users).
  3. UNION ALL combines the results. UNION (without ALL) would remove duplicates, which might be desirable if an order could hypothetically exist without a user, and a user without an order, and they both appeared in the left/right part of the query. For a true “all rows from both sides with NULLs where no match,” UNION ALL followed by a distinct on the result is effectively FULL JOIN.

Because of foreign key constraints, FULL JOIN on users and orders in a well-designed scriptbuzz_db won’t show NULL users for orders, as every order must have a user.

When to Use Which Join?

  • INNER JOIN: When you only want to see data where there’s a match in both tables. (e.g., “users who have placed orders,” “products that have been ordered”).
  • LEFT JOIN: When you want to see all data from the “left” table, and matching data from the “right” table (with NULLs for non-matches on the right). (e.g., “all users and their orders if any,” “all products and their order counts”). This is the most frequently used Outer JOIN.
  • RIGHT JOIN: Less common, but useful if your primary focus is the “right” table. Can always be rewritten as a LEFT JOIN by swapping table order.
  • FULL JOIN: When you want to see all data from both tables, regardless of matches, with NULLs where there are no matches. (e.g., “all users and all products, and how they relate through orders, showing those with no relation”). Use the UNION simulation in MySQL/MariaDB.

Common Pitfalls and Best Practices

  • Understanding NULLs: Be clear about which side of the join will produce NULLs for non-matches. This is the defining characteristic of Outer JOINs.
  • Join Condition: A correct ON clause is essential. An incorrect one can lead to massive result sets or incorrect pairings.
  • Performance: Outer JOINs can be more expensive than INNER JOINs, especially on large datasets. Ensure your join columns are indexed.
  • Choose the Right Join: Select the join type that accurately reflects the relationship you want to display and the data you need to include (or exclude).

Notes:

  • Outer JOINs (LEFT, RIGHT, FULL) include rows even when there’s no match in the joined table, populating unmatched columns with NULL.
  • LEFT JOIN: Returns all rows from the left table and matching rows from the right. (Most common outer join).
  • RIGHT JOIN: Returns all rows from the right table and matching rows from the left. (Less common, can be swapped for LEFT JOIN).
  • FULL JOIN: Returns all rows when there’s a match in either table. (Not directly supported in MySQL/MariaDB, simulated with LEFT JOIN UNION RIGHT JOIN).
  • The choice of join type depends entirely on whether you want to include non-matching rows from one or both tables.

Small Exercise

Using your scriptbuzz_db:

  1. List all users, and for each user, show the total total_amount of their orders. Include users who have placed no orders (their total amount should be NULL or 0). (Hint: Use LEFT JOIN, GROUP BY, and COALESCE or IFNULL for the NULLSUM if you want 0 instead of NULL).
    • Bonus: Use COALESCE(SUM(o.total_amount), 0) or IFNULL(SUM(o.total_amount), 0) to display 0 instead of NULL for users without orders.
  2. List all products, and show the product_id, name, and the order_id of any order_items they are part of. Include products that have never been part of an order_item.
  3. Find all categories (from the products table) and the number of distinct products in each. Include categories that might not have any products (if your categories were in a separate table, but for now just group by the existing category column in products). Show NULL for categories if they don’t have products (or 0 with COALESCE).

You’re now equipped with the full range of powerful joining techniques! This opens up a vast array of possibilities for querying complex, related data in your applications. Next, we’ll look at subqueries, another way to combine queries.

📘 Continue to the Next Chapter

Subqueries & Nested Queries

Learn how to use subqueries inside SELECT, INSERT, UPDATE, and DELETE statements to perform complex SQL operations.

➡️

🚀 Explore Popular Learning Tracks