SQL JOINs Explained With Simple Examples

Real databases split information across tables. Customers live in one, their orders in another. A JOIN is how you put them back together in a single result.

The names, INNER, LEFT, RIGHT, and FULL, only differ in one thing: what happens to rows that don't have a match on the other side. Once you see that, the rest is easy.

Two small tables

Run this in the SQL editor to follow along. Notice that Dee has no orders, and one order belongs to a customer who isn't in the customers table.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, item TEXT, amount REAL);

INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cy'), (4, 'Dee');
INSERT INTO orders VALUES
  (1, 1, 'Book', 12.50),
  (2, 1, 'Pen', 2.00),
  (3, 2, 'Lamp', 30.00),
  (4, 3, 'Desk', 150.00),
  (5, 99, 'Mystery box', 9.99);

The customer_id column in orders points at customers.id. That's the link we'll join on.

INNER JOIN: only the matches

An INNER JOIN returns rows only when both sides match. Everything else disappears.

SELECT customers.name, orders.item, orders.amount
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id
ORDER BY customers.name, orders.item;

name  item  amount
----  ----  ------
Ana   Book  12.5
Ana   Pen   2.0
Ben   Lamp  30.0
Cy    Desk  150.0

Dee is gone, because she has no orders. The mystery box is gone too, because no customer matches id 99. Ana appears twice, since she has two orders, which is normal. A join produces one row for each matching pair.

INNER JOIN and plain JOIN mean the same thing.

LEFT JOIN: keep everyone on the left

A LEFT JOIN keeps every row from the first (left) table. When there's no match, the right-side columns are filled with NULL.

SELECT customers.name, orders.item
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
ORDER BY customers.name, orders.item;

name  item
----  ----
Ana   Book
Ana   Pen
Ben   Lamp
Cy    Desk
Dee   NULL

Now Dee shows up, with an empty item. This is the join you'll use most often, whenever the question is "show me all the X, and their Y if they have any."

Find the rows with no match

Here's a very useful trick. Do a LEFT JOIN, then keep only the rows where the right side is NULL. That gives you the left rows that have no partner.

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

name
----
Dee

"Customers who never ordered" is one of the most common real-world questions. The same pattern finds users with no logins, products with no sales, and posts with no comments.

RIGHT JOIN: the mirror image

A RIGHT JOIN keeps every row from the second (right) table. It's a LEFT JOIN with the tables swapped.

SELECT customers.name, orders.item
FROM customers
RIGHT JOIN orders ON orders.customer_id = customers.id
ORDER BY orders.id;

name  item
----  -----------
Ana   Book
Ana   Pen
Ben   Lamp
Cy    Desk
NULL  Mystery box

The mystery box appears now, with no customer name. Most people just swap the table order and use LEFT JOIN, since it reads more naturally. Some databases, like older SQLite versions, don't support RIGHT JOIN at all. The CompileZ editor does.

FULL JOIN: keep everything from both

A FULL JOIN keeps all rows from both sides. Matches are paired up, and the leftovers on either side get NULLs.

SELECT customers.name, orders.item
FROM customers
FULL JOIN orders ON orders.customer_id = customers.id
ORDER BY customers.name, orders.item;

name  item
----  -----------
NULL  Mystery box
Ana   Book
Ana   Pen
Ben   Lamp
Cy    Desk
Dee   NULL

You see Dee with no order and the mystery box with no customer, in one result. It's great for reconciliation, such as comparing two lists to find what's missing from each. MySQL doesn't support FULL JOIN directly, so there you'd combine a LEFT and a RIGHT join with UNION.

Quick reference

• INNER JOIN: only rows that match on both sides.
• LEFT JOIN: all left rows, plus matches from the right.
• RIGHT JOIN: all right rows, plus matches from the left.
• FULL JOIN: all rows from both sides.

Joining and summarizing

You'll often combine a join with GROUP BY to answer questions like "how much has each customer spent?"

SELECT customers.name,
       COUNT(orders.id) AS order_count,
       COALESCE(SUM(orders.amount), 0) AS total_spent
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.id, customers.name
ORDER BY total_spent DESC;

name  order_count  total_spent
----  -----------  -----------
Cy    1            150.0
Ben   1            30.0
Ana   2            14.5
Dee   0            0

Two details matter here. COUNT(orders.id) counts only real orders, while COUNT(*) would count Dee's empty row as 1. And COALESCE swaps NULL for 0, so Dee's total is 0 instead of blank.

Mistakes that cause wrong results

Forgetting the ON condition. Without it, every row pairs with every other row, and you get a huge result called a cross join. 4 customers times 5 orders gives you 20 rows of nonsense.

Filtering the right table in WHERE after a LEFT JOIN. This quietly turns your LEFT JOIN into an INNER JOIN, because NULL rows fail the condition. Put the filter in the ON clause instead:

SELECT customers.name, orders.item
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id AND orders.amount > 20
ORDER BY customers.name;

name  item
----  ----
Ana   NULL
Ben   Lamp
Cy    Desk
Dee   NULL

Everyone is still listed. Only big orders are attached.

Surprise duplicates. If you join to a table where one row matches many, your totals can get inflated. Check row counts before and after a join when numbers look too high.

Ambiguous column names. If both tables have an id column, write customers.id or give tables short aliases such as c and o.

Key takeaways

• A join pairs up rows using a matching condition in ON.
• INNER keeps matches only. LEFT keeps every left row. FULL keeps everything.
• LEFT JOIN plus IS NULL finds rows with no match.
• Put conditions on the right table in ON, not WHERE, when you want to keep unmatched left rows.