SQL JOINs Explained: INNER, LEFT, RIGHT, and FULL with Real Examples

If you have ever stared at four Venn diagrams trying to remember which JOIN keeps which rows, this article is for you. We're going to skip the abstract circles and use one tiny dataset, four queries, and the actual result tables each query produces. By the end, you'll be able to predict a JOIN's output before you run it, which is the skill that actually matters.

All examples use PostgreSQL, but everything here works in any modern SQL database with at most cosmetic changes.

Why JOINs exist in the first place

Relational databases split data across multiple tables on purpose. Imagine storing the customer's name on every order row. If a customer places 500 orders and then changes their name, you'd have 500 rows to update, and if you miss one, your data now disagrees with itself.

So instead, you store each fact once. Customers live in a customers table. Orders live in an orders table, and each order carries a customer_id that points back to its customer. This is called normalization.

The trade-off: your data is now in pieces. A JOIN is how you put the pieces back together at query time. That's all a JOIN is: a way to line up rows from two tables using a matching column.

The dataset we'll use for every example

Here's the setup. Run this in any PostgreSQL database and you can follow along exactly:

CREATE TABLE customers (
    id   integer PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE orders (
    id          integer PRIMARY KEY,
    customer_id integer REFERENCES customers (id),
    total       numeric(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
    (1, 'Amira'),
    (2, 'Ben'),
    (3, 'Chloe'),
    (4, 'Dario');

INSERT INTO orders (id, customer_id, total) VALUES
    (101, 1, 42.00),
    (102, 1, 18.50),
    (103, 2, 27.00),
    (104, NULL, 99.00);

Which gives us these two tables:

customers

orders

Two details are deliberate, because they're what make the four JOIN types produce different results:

• Chloe and Dario have no orders. They exist only on the customers side.
• Order 104 has no customer. Think of it as a guest checkout. It exists only on the orders side.

Keep those two facts in mind. Every difference between the JOINs comes down to how they treat rows with no match on the other side.

INNER JOIN: only the matches

INNER JOIN returns a row only when both sides match. No match, no row.

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.id;

Notice what's missing. Chloe and Dario are gone because they have no orders. Order 104 is gone because it has no customer. INNER JOIN quietly drops anything without a partner, which is exactly what you want most of the time, and exactly what bites you when you forget it.

Also notice Amira appears twice. A JOIN matches rows, not tables: Amira has two orders, so she pairs with each one. This one-to-many multiplication is normal, but it matters later when we talk about mistakes.

By the way, plain JOIN with no keyword means INNER JOIN. They're the same thing.

LEFT JOIN: keep everything on the left

LEFT JOIN keeps every row from the left table (the one named first), matched or not. Where there's no match on the right, the right side's columns come back as NULL.

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Chloe and Dario are back, padded with NULLs. Order 104 is still missing, because LEFT JOIN only protects the left side.

Those NULL-padded rows are useful. The classic "customers who have never ordered" query is a LEFT JOIN plus a NULL check:

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

That returns Chloe and Dario. This pattern, LEFT JOIN then filter for the missing side, is one of the most common query shapes in real work. Learn it once and you'll use it forever.

RIGHT JOIN: keep everything on the right

RIGHT JOIN is the mirror image: every row from the right table survives, and the left side gets NULL-padded when there's no match.

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.id;

Now order 104 shows up with a NULL name, and Chloe and Dario are gone.

Honest advice: you will rarely see RIGHT JOIN in production code. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, and most teams standardize on LEFT because queries read top-to-bottom more naturally that way. Know what it does, then reach for LEFT JOIN in practice.

FULL JOIN: keep everything, period

FULL JOIN (or FULL OUTER JOIN, same thing) keeps every row from both sides. Matches pair up; everything else gets NULL-padded on whichever side is missing.

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
FULL JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Six rows: three matches, two customers with no orders, one order with no customer. Nothing is dropped.

FULL JOIN shines in reconciliation work, like comparing your database against a payment provider's export to find records that exist on only one side. Day to day, it's the rarest of the four.

How NULL behaves in JOINs

NULL follows one rule that explains a lot of confusing results: NULL never equals anything, not even another NULL.

That's why order 104 never matches a customer. Its customer_id is NULL, and NULL = anything is not true, so the ON condition never fires for it. Even if you somehow had a NULL key on both sides, they still would not match each other.

The same rule applies in WHERE clauses. This query returns zero rows, silently:

SELECT * FROM orders WHERE customer_id = NULL;  -- always empty

You must use IS NULL instead:

SELECT * FROM orders WHERE customer_id IS NULL;  -- finds order 104

If a query is mysteriously returning nothing, check whether you compared something to NULL with =.

Common JOIN mistakes

The accidental cross join

Old-style SQL let you list tables with commas. If you do that and forget the WHERE clause that links them, you get every customer paired with every order:

SELECT c.name, o.id
FROM customers AS c, orders AS o;  -- 4 x 4 = 16 rows

Sixteen rows from tables of four each. On real tables, that's millions of garbage rows and a query that never finishes. The fix is habit: always use explicit JOIN ... ON syntax. PostgreSQL will refuse to run a JOIN without an ON clause, so the explicit form makes this mistake nearly impossible to type.

Filtering a LEFT JOIN in WHERE instead of ON

This one is subtle and extremely common. Say you want all customers, with their orders over 20 shown alongside:

-- Wrong: this silently turns into an INNER JOIN
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.total > 20;

Chloe and Dario vanish. Why? Their o.total is NULL, and NULL > 20 is not true, so the WHERE clause throws their rows away. Your carefully chosen LEFT JOIN just behaved like an INNER JOIN.

The fix is to put conditions about the right table into the ON clause, where they affect matching instead of filtering:

-- Right: keeps all customers, only matches orders over 20
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.total > 20;

Now Chloe and Dario stay, with NULL order columns. Rule of thumb: in a LEFT JOIN, conditions on the left table go in WHERE, conditions on the right table go in ON.

Forgetting that matches multiply

Because Amira has two orders, she appears twice in every join above. If you then compute something per customer without accounting for that, numbers inflate. Joining a third table with its own one-to-many relationship multiplies rows again. When an aggregate looks too big, count your rows before you aggregate them.

Which JOIN should you use?

In practice, INNER and LEFT cover well over 90 percent of the queries you'll write. Master those two, know the NULL rules, and the rest follows.

Frequently asked questions

Is JOIN the same as INNER JOIN?

Yes. If you write JOIN with no qualifier, SQL treats it as INNER JOIN. Likewise, LEFT JOIN and LEFT OUTER JOIN are identical; the OUTER keyword is optional decoration.

Why does my JOIN return more rows than either table?

Because JOINs match rows, not tables. If one customer has three orders, that customer contributes three rows. If both sides of a join can have multiple matches, the counts multiply. That's correct behavior, but you need to aggregate deliberately, usually with GROUP BY, if you want one row per entity.

Do JOINs make queries slow?

Not inherently. Databases are built to join efficiently, but they need indexes on the joining columns to do it. In our example, orders.customer_id should be indexed. If a join is slow, run EXPLAIN on the query and check whether the database is scanning a whole table instead of using an index.

How do I join more than two tables?

Chain them: FROM a JOIN b ON ... JOIN c ON .... Each JOIN takes the result built so far and matches it against the next table. The same NULL and multiplication rules apply at every step, so build multi-table joins one join at a time and check the row counts as you go.

Where to go from here

The fastest way to make JOINs stick is to run them against real tables and watch the row counts change as you switch JOIN types. Take the dataset from this article, break it, extend it, and query it until the results stop surprising you.

If you want that practice with instant feedback, structured exercises, and a tutor that hints instead of handing you answers, the SQL & Databases course covers joins, aggregation, and everything up through production-grade queries, starting from zero.