LEFT and RIGHT Joins in PostgreSQL

Ever run a query expecting every row from one table to show up, only to watch rows silently disappear because they had no match in the other table? That's the moment you realize joins are not all created equal. In this lesson, you'll learn to use LEFT and RIGHT joins in PostgreSQL to keep all rows from one side of a relationship, even when there's no match on the other side. By the end, you'll not only understand the difference between these outer joins but also know exactly when to reach for each—and how to fix the surprising gotchas they can introduce.

The problem this lesson solves

Standard INNER JOIN is the workhorse of SQL, but it has a blind spot: it only returns rows that have matching values in both tables. When you're building reports or auditing data, that's often not what you need.

Imagine you run an e-commerce site. You want to list all products, even those that have never been ordered. An INNER JOIN between products and orders would silently drop those products—zero orders means no match, so the product vanishes from your results. You'd be left with an incomplete picture of your catalog, and worse, you might not even realize it.

The same problem appears in countless scenarios:

• You need a list of all employees and their assigned departments, including those without a department.
• You're generating a matrix of all possible combinations and need to see which ones are missing.
• You're comparing two datasets and want to highlight records that exist in one but not the other.

In all these cases, LEFT JOIN (or its sibling RIGHT JOIN) is the tool that keeps every row from one side, filling gaps with NULL when no match exists. This lesson gives you a mental model and hands-on practice to make these joins second nature.

Core concept / mental model

Think of joins as a way to glue two tables together along a shared column. The difference between join types is simply which side gets to keep all its rows.

• An INNER JOIN keeps only the rows that match on both sides.
• A LEFT JOIN keeps all rows from the left table (the one in the FROM clause) and only matching rows from the right table. Unmatched right-side columns become NULL.
• A RIGHT JOIN is the mirror image: it keeps all rows from the right table (the one in the JOIN clause) and only matching rows from the left table.

A helpful analogy: imagine two circles in a Venn diagram. The left circle is your left table, the right circle is your right table. An inner join returns only the overlapping area. A left join returns the entire left circle plus the intersection—but not the right-only part. Right join is the opposite.

In practice, LEFT JOIN is far more common than RIGHT JOIN. Most developers write queries starting from a "base" table (the left one) and add details from other tables with LEFT JOIN. RIGHT JOIN exists mainly for symmetry, and you'll often see it rewritten as a LEFT JOIN by swapping the table order—both produce identical results.

How it works step by step

Let's break down the mechanics of a LEFT JOIN in a step-by-step way.

1. Start with the left table: PostgreSQL reads every row from the table in the FROM clause.
2. Match rows in the right table: For each left row, it looks for rows in the joined table where the join condition is true (e.g., a.id = b.a_id).
3. Attach matched data: If one or more matches exist, the right table's columns are added to the result row.
4. Handle no match: If no match exists, the result still includes the left row, but every column from the right table is set to NULL.
5. Repeat for all rows: This process happens for every row in the left table, so no left row is ever lost.

For a RIGHT JOIN, the process is identical but the tables are swapped: every row from the right table is preserved, and unmatched left columns become NULL.

The ON clause versus WHERE

One critical detail: the join condition in the ON clause controls match creation, while WHERE filters the final result. If you add a condition on a right-table column to WHERE, you effectively turn your LEFT JOIN into an INNER JOIN because rows with NULL values (from no match) are eliminated. To filter unmatched rows properly, put the condition in the ON clause or use IS NULL in WHERE.

Hands-on walkthrough

Let's make this concrete. First, create a small sample dataset—two tables: products and orders.

-- Create sample tables
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id),
    quantity INT
);

-- Insert data
INSERT INTO products (name) VALUES ('Laptop'), ('Mouse'), ('Keyboard');
INSERT INTO orders (product_id, quantity) VALUES (1, 2), (1, 1), (3, 5);

Note: product id 2 ('Mouse') has no orders.

Basic LEFT JOIN example

Now, list all products with their total ordered quantity, including products with no orders.

SELECT p.name, COALESCE(SUM(o.quantity), 0) AS total_ordered
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
GROUP BY p.name
ORDER BY p.name;

Expected output:

name     | total_ordered
---------+---------------
Keyboard |            5
Laptop   |            3
Mouse    |            0

Notice how 'Mouse' appears with a total of 0, thanks to COALESCE turning NULL into 0. Without LEFT JOIN, 'Mouse' would be missing entirely.

RIGHT JOIN example

RIGHT JOIN is less common, but here's a scenario: you want all orders and the product name, even if a product has been deleted (though foreign keys usually prevent that). Here's the syntax:

SELECT p.name, o.quantity
FROM products p
RIGHT JOIN orders o ON p.id = o.product_id;

This keeps all orders, and if an order references a nonexistent product, p.name would be NULL. In our data, every order has a valid product, so the result looks like a normal inner join.

LEFT JOIN to find unmatched rows

A classic use of LEFT JOIN is finding rows in one table that have no match in another. For example, list products that have never been ordered:

SELECT p.name
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.id IS NULL;

Expected output:

name
-----
Mouse

Here, WHERE o.id IS NULL selects only the left rows that found no match—a clean way to detect orphans or missing relationships.

Compare options / when to choose what

Pro tip: If you find yourself writing a RIGHT JOIN, consider swapping the table order and using LEFT JOIN instead. It's more intuitive for most readers because you naturally think of the "main" table as the left one.

Alternative: FULL OUTER JOIN

There's also FULL OUTER JOIN, which keeps all rows from both tables, filling NULL on either side when no match exists. It's useful for comparing two lists or finding mismatches in both directions. LEFT and RIGHT are just special cases of FULL OUTER JOIN where you keep only one side complete.

Troubleshooting & edge cases

1. Filtering in WHERE defeats the LEFT JOIN

If you put a condition on a right-table column in WHERE, you eliminate the NULL rows. For example, this query returns only products with orders:

SELECT p.name
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.quantity > 0;  -- Oops! This filters out 'Mouse'

Fix: move the condition into the ON clause, or explicitly check for NULL.

2. Duplicate rows from multiple matches

If a left row matches multiple rows in the right table, you get duplicate left rows. For example, product 'Laptop' has two orders, so joining without aggregation yields two rows for 'Laptop'. To avoid confusion, use aggregation (SUM, COUNT) or DISTINCT when you only need one row per left item.

3. NULL values in the join column

If the join column contains NULL, it won't match anything, because NULL = NULL is not true. This can cause unexpected missing matches. Use COALESCE or IS NOT DISTINCT FROM if you need to treat NULL as equal.

4. Performance considerations

LEFT JOIN is not inherently slower than INNER JOIN, but it can be if the query planner chooses a less efficient plan. Always check the execution plan with EXPLAIN ANALYZE when dealing with large tables, and ensure you have indexes on the join columns.

What you learned & what's next

You now understand the core idea behind LEFT and RIGHT joins in PostgreSQL: they preserve all rows from one side of a join, filling gaps with NULL when no match exists. You practiced writing LEFT JOIN queries, used RIGHT JOIN in a symmetrical scenario, and learned to find unmatched records with a WHERE ... IS NULL pattern. You also picked up critical troubleshooting skills—like avoiding WHERE filters that silently convert outer joins to inner joins.

These skills are foundational for more advanced SQL topics. Next in the track, you'll likely explore FULL OUTER JOIN and union operations, which build on the same mental model to combine even more complex datasets. You're now well-equipped to write queries that give you the complete picture, not just the matching pieces.