SQL Joins Explained with Examples: INNER, LEFT, RIGHT

Why does data live in two tables in the first place?

A shop could keep everything in one giant spreadsheet: customer name, city, then every order repeated on the same rows. But "Ayesha, Lahore" would be retyped for every order she places — and one typo later, "Ayesha" and "Ayesh" look like two customers.

Instead, databases split the data: a customers table holds each customer once, an orders table holds each order once, and the orders table carries a customer_id — a pointer back to the customer's row. This is called *normalisation*, but the name does not matter; what matters is that no fact is stored twice, so no fact can contradict itself. Joins are how you put the pieces back together when you need to ask a question.

The shop dataset we will query

The sample data is deliberately small — four customers, four orders — and deliberately imperfect: order 104 points to customer 9, who does not exist, and Sara and Usman have no orders. Real data always has these rough edges, and they are exactly what makes the three join types behave differently.

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

INSERT INTO customers VALUES (1,'Ayesha','Lahore'),(2,'Bilal','Karachi'),
  (3,'Sara','Islamabad'),(4,'Usman','Lahore');
INSERT INTO orders VALUES (101,1,'Keyboard',4500),(102,1,'Mouse',1800),
  (103,2,'Monitor',32000),(104,9,'Headset',6500);

You can run everything below on any free SQLite setup (or DB Browser for SQLite) — copy the CREATE TABLE and INSERT lines first, then each query.

What does INNER JOIN do — and what does it throw away?

INNER JOIN is the default join: it returns only rows where both tables have a match.

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

Real output (exactly as returned):

id   name    item      amount
101  Ayesha  Keyboard  4500
102  Ayesha  Mouse     1800
103  Bilal   Monitor   32000

Three rows, not four: order 104 vanished, because customer 9 does not exist. That is INNER JOIN's one danger — it silently drops anything that does not match. When the join is on a clean, enforced foreign key, nothing is dropped and it is exactly what you want ("show me every order with its customer"). When the data is messy, the missing rows might be the story — a payment recorded against a deleted account is worth knowing about, not hiding.

What does LEFT JOIN do — and when is it the right choice?

LEFT JOIN keeps every row from the table on the left of the JOIN keyword (here, customers), matching orders where it can and filling NULLs where it cannot.

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

Real output (exactly as returned):

name    item      amount
Ayesha  Keyboard  4500
Ayesha  Mouse     1800
Bilal   Monitor   32000
Sara
Usman

Sara and Usman appear with empty item and amount columns — NULLs, SQL's "no value here". This is the analyst's most-used join, because the questions businesses ask are almost always left-flavoured: "which customers have not ordered yet?", "show me all products and how many units each sold (including the zero sellers)?". LEFT JOIN never hides a row you started with.

What does RIGHT JOIN do — and do I actually need it?

RIGHT JOIN is the mirror image: it keeps every row from the table on the right of the JOIN keyword (here, orders).

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

Real output (exactly as returned):

name    item      amount
Ayesha  Keyboard  4500
Ayesha  Mouse     1800
Bilal   Monitor   32000
        Headset   6500

Order 104 is back — with a NULL name, flagging the orphaned order the INNER JOIN hid. Note that RIGHT JOIN worked here in SQLite 3.45.1 (verified; RIGHT JOIN has been supported since SQLite 3.39.0, 2022, though older tutorials and older SQLite versions claim it is not). And this query returns byte-identical rows to SELECT ... FROM orders LEFT JOIN customers ON ... — swapping the tables converts any RIGHT JOIN into a LEFT JOIN. That is why most analysts write LEFT JOINs almost exclusively: RIGHT JOIN adds no power, and reading left-to-right matches how the query is read.

Worked example: "How much did each customer spend?"

Joins combine with aggregation — the subject of the next tutorial in this series — to answer real business questions. Here is "total spend per customer, including customers who spent nothing":

SELECT customers.name, SUM(orders.amount) AS total_spent
FROM customers LEFT JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.name;

Real output (exactly as returned):

name    total_spent
Ayesha  6300
Bilal   32000
Sara
Usman

Why LEFT and not INNER? With an INNER JOIN, Sara and Usman would vanish and the report would quietly pretend every customer buys something. With LEFT, they appear with NULL totals — which is the honest answer to "how much did Sara spend?" when she placed no orders: *nothing yet*, not "she does not exist". When you present this to a manager, convert the NULLs to 0 in the SELECT (SUM of no rows is NULL in SQL; most reporting tools or a COALESCE(SUM(orders.amount), 0) handle the display) — but keep the LEFT JOIN, because the rows matter.

Common mistakes

• Forgetting the ON clause. FROM orders JOIN customers with no ON produces a cross join — every order paired with every customer (16 rows here). If your result is suddenly huge, check for the missing ON.
• Joining on the wrong columns. ON orders.id = customers.id matches order numbers to customer numbers — syntactically valid, semantically nonsense. The ON condition should relate the pointer (orders.customer_id) to the thing it points at (customers.id).
• Using INNER when you meant LEFT. "List all customers and their orders" with an INNER JOIN silently excludes customers who never ordered — exactly the customers the business most wants to see. Match the join to the question, not to habit.
• Ignoring the NULLs. NULL is not zero and not an empty string — amount = NULL is never true in a WHERE clause; the correct test is amount IS NULL. Use it when hunting for unmatched rows.
• Selecting * from a join. Both tables have an id column, so SELECT * gives you two columns both called id. Name the columns you want explicitly, and qualify them with the table name as the examples above do.

Frequently asked questions