Understanding LEFT and RIGHT JOINs

The Concept of an Outer Join

While INNER JOIN is exclusive, returning only the matching rows, OUTER JOIN is inclusive. It starts with the matching rows (the INNER JOIN result) and then adds the non-matching rows from one or both tables.

The keywords LEFT and RIGHT simply tell the database which table's non-matching rows to keep.

• LEFT JOIN: Keeps all rows from the "left" table (the one listed after FROM).
• RIGHT JOIN: Keeps all rows from the "right" table (the one listed after the JOIN keyword).

For columns from the table that doesn't have a matching row, the database fills in the value NULL.

This set of Venn diagrams is a perfect visual summary of the concept. Notice how LEFT and RIGHT joins include the non-overlapping parts of one of the circles.

LEFT JOIN: Prioritizing the Left Table

Let's imagine a common business request: "Show me all employees and the department they work in. Make sure no employees are missing from the list, even if their department isn't registered in the departments table yet."

An INNER JOIN would fail here because it would drop any employee whose department_id doesn't exist in the departments table. This is the perfect use case for a LEFT JOIN.

The following video provides an excellent walkthrough. It starts with the problem (an INNER JOIN that loses employees) and shows how a LEFT JOIN solves it. The presenter also introduces a simple but powerful "formula" for thinking about how it works.

As you saw, the core logic is:

1. Perform an INNER JOIN to find all the matching records.
2. Go back to the left table and add all rows that were not part of the INNER JOIN result.
3. For these added rows, populate the columns from the right table with NULL.

For a textual reference, this article provides a clean example using Customers and Orders tables.

RIGHT JOIN: The Mirror Image

A RIGHT JOIN does the exact same thing as a LEFT JOIN, but in reverse. It keeps all records from the table on the right side of the JOIN keyword and fills in NULLs for the left-table columns where there's no match.

The formula is symmetrical: RIGHT JOIN = INNER JOIN + additional records from the right table.

Let's continue with the same video to see this in action.

Practical Tip: Any RIGHT JOIN can be rewritten as a LEFT JOIN by simply swapping the order of the tables. Many developers prefer to use LEFT JOIN exclusively for consistency. However, you must understand RIGHT JOIN because you will encounter it in code written by others.

SELECT
    c.CustomerName,
    o.OrderID
FROM
    Orders o
RIGHT JOIN
    Customers c ON o.CustomerID = c.CustomerID;

SELECT
    c.CustomerName,
    o.OrderID
FROM
    Customers c
LEFT JOIN
    Orders o ON c.CustomerID = o.CustomerID;

A Critical Detail: NULL in Join Keys

In the last lesson, we noted that NULL = NULL is never true in SQL. This has an important implication for outer joins. If the join key column in both tables contains a NULL value, the database will not consider them a match.

This video gives a concise explanation of this behavior, which is a key low-level detail to remember.

Finding What's Missing: A Powerful Pattern

One of the most valuable uses of an outer join is to find records in one table that do not have a corresponding record in another. For example:

• Customers who have never placed an order.
• Products that have never been reviewed.
• Users who have never logged in.

The pattern is simple and powerful:

1. Perform a LEFT JOIN from the primary table (e.g., Customers) to the secondary table (e.g., Orders).
2. Add a WHERE clause that filters for rows where the primary key of the secondary table is NULL.

A NULL in the secondary table's key proves that no match was found.

Looking back at the Venn diagram image from the beginning of the lesson, you can see this pattern illustrated in the bottom-left corner for finding customers with no orders.

A Deeper Dive: ON vs. WHERE Clause Filtering

Your interest in the low-level "why" makes this next point especially important. Where you place your filter conditions with an outer join can dramatically change your results. This is a common source of bugs for developers.

• A condition in the ON clause is applied before the join happens. It defines which rows are considered a match.
• A condition in the WHERE clause is applied after the join has been completed. It filters the final result set.

When you LEFT JOIN, putting a filter on the right table in the WHERE clause will often implicitly turn your LEFT JOIN into an INNER JOIN. Why? Because the WHERE clause will filter out all the rows where the right table's columns are NULL, which defeats the purpose of the outer join!

The following article explains this critical distinction perfectly.

Time to Practice

To solidify these concepts, let's set up a small practice database and run a few queries. The following article provides the CREATE TABLE and INSERT statements for a simple Customers and Orders database.

Now, using the tables you just created, write the following queries:

1. Write a query that lists all customers and the Product they ordered. Customers who have not ordered anything should still appear in the list.
2. Write a query that lists all products that have been ordered and the name of the customer who ordered them.
3. Write a query that returns the names of only the customers who have NOT placed an order.

SELECT
    c.CustomerName,
    o.Product
FROM
    Customers c
LEFT JOIN
    Orders o ON c.CustomerID = o.CustomerID;

SELECT
    o.Product,
    c.CustomerName
FROM
    Orders o
INNER JOIN
    Customers c ON o.CustomerID = c.CustomerID;

SELECT
    c.CustomerName
FROM
    Customers c
LEFT JOIN
    Orders o ON c.CustomerID = o.CustomerID
WHERE
    o.OrderID IS NULL;

Conclusion

You have now added two more essential join types to your SQL toolkit. Understanding the difference between INNER and OUTER joins is fundamental to writing correct and effective queries.

Key Takeaways:

• LEFT JOIN returns all rows from the left table and matched rows from the right table.
• RIGHT JOIN returns all rows from the right table and matched rows from the left table.
• For non-matching rows, the columns from the other table are filled with NULL.
• The pattern LEFT JOIN ... WHERE right_table.key IS NULL is the standard way to find records that lack a relationship.
• Be very careful where you place your filters: ON applies the condition during the join, while WHERE filters the final result, which can accidentally negate your OUTER JOIN.

Preview of the Next Lesson:
Our queries are getting more complex. As we start joining more than two tables, the syntax can become long and hard to read. In the next lesson, we will learn how to apply table aliases to make our multi-table join queries cleaner, more readable, and easier to maintain—a crucial best practice for any developer.