The INNER JOIN is the default JOIN, the one you already used without naming it in the previous lesson and the one you'll write in eight out of every ten queries of your professional life. Its rule is absolutely simple: only the rows that find a partner on both sides survive. Everything else disappears, silently and without warning.

And there's the trap. An INNER JOIN never raises an error for losing rows; it simply returns fewer than you expected. In this lesson you'll learn not only how to write it, but how to predict how many rows it returns and why, which is the skill that separates someone who understands JOINs from someone who copies them. You'll watch three GreenStore customers and half of its orders disappear, and you'll understand that those disappearances are the very definition of the operator.

1. INNER JOIN versus plain JOIN
2. The set diagram
3. Progressive examples over GreenStore
4. The canonical four-table query
5. Which rows get lost and why
6. Extra conditions: ON versus WHERE
7. Non-unique keys and row multiplication
8. The order of the tables doesn't change the result
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

The two forms below are exactly the same query:

INNER is optional because the SQL standard defines the unqualified JOIN as inner. It's the opposite of what happens with LEFT OUTER JOIN, where the optional word is OUTER.

Which one should you write? There are two schools. One prefers JOIN for brevity; the other prefers INNER JOIN because, in a query that mixes several types, seeing the word INNER next to a LEFT makes the intent explicit and stops anyone thinking a modifier was forgotten.

Course convention: we'll write JOIN when the whole query is inner, and an explicit INNER JOIN when it lives alongside a LEFT, a RIGHT or a FULL in the same query. It's the most widespread practice in professional teams.

The classic way of visualising JOINs is with two overlapping sets. The INNER JOIN returns only the intersection:

And as a flow of rows, picking up the mental model from 03-01:

That red box —"discarded on both sides"— is all you need to remember about the INNER JOIN. Lessons 03-03, 03-04 and 03-05 consist precisely of recovering what gets thrown away here.

You already saw this in 03-01; we bring it back as a starting point, now extended with the supplier. Three tables, two ONs:

Notice a detail that's only possible with a JOIN: we've filtered by the category's name, not by its id. Before this module you'd have had to look up by hand that "Natural cosmetics" is category 2 and write WHERE category_id = 2. Now the query reads the way you think.

The question we closed module 2 with: who placed each order.

20 rows, the same number orders has. There at last is "Camille Dubois" where there used to be a 9.

Two observations:

• Names repeat: Lucía Martínez Soler appears three times because she placed three orders (1, 5 and 15). That's normal: we're joining through the "many" side of the 1:N relationship.
• The concatenation c.name || ' ' || c.last_name is the one from 02-02, warning about NULL included. Here it's safe because both columns are NOT NULL.

(First 10 of 47 rows.)

Here you can see something we could only guess at in module 2: line 1 has unit_price 11.95, while the oil costs €12.50 today. It's the historical price 01-06 talked about. A classic mistake would be calculating the amount with p.price instead of ol.unit_price: you'd get €25.00 instead of €23.90 and you'd be rewriting the company's commercial history.

Course rule: a line's amount is always calculated with ol.unit_price, never with p.price. The products table says what it costs today; order_lines says what was charged back then.

This is the most important query of the course. It answers "what did each customer buy and for how much?" and it needs four tables: the detail is in order_lines, the customer hangs off orders and the product's name off products.

Look at the shape of the path: it isn't a linear chain, it's a star with order_lines at the centre. Two branches come out of it: one towards the order and its customer, another towards the product. That's normal when the starting table has several foreign keys.

(First 12 of 47 rows.)

47 rows: one per order line. Keep this query, because it's the basis of nearly everything that comes next. In module 4 you'll add GROUP BY c.id and SUM(amount) to answer "how much has each customer spent?"; in module 7 you'll use it as a subquery; in module 10 you'll turn it into a view. The skeleton doesn't change.

Four tables, three ON conditions. The rule from 03-01 holds again: N tables, N-1 pairings.

Here we reach the heart of the lesson. An INNER JOIN silently discards partnerless rows, and in GreenStore that has two very visible consequences.

customers has 15 rows. Let's see who survives an INNER JOIN with orders:

12 rows, not 15. Customers 13 (Núria Bosch Ferrer), 14 (Hugo Iglesias Pardo) and 15 (Inés Carrasco Vega) are missing. Why? Because none of them has placed an order, so in the cartesian product there's no combination in which o.customer_id equals 13, 14 or 15. The ON condition doesn't find them and they disappear.

The balance of counts:

The three figures are different and all three are correct: they answer different questions. If you're asked for "the list of customers with their activity" and you hand over 12 out of 15, you've erased three people from the report.

Warning: that DISTINCT is exactly the symptom 02-04 talked about. It shows up here because we're using a tool —the INNER JOIN— that isn't the right one for the question "which customers have bought". The right tool arrives in 03-03.

The same phenomenon, from the other side. orders.employee_id allows NULL because web orders carry no assigned sales rep:

10 rows out of 20. Orders 1, 3, 5, 7, 9, 11, 13, 15, 17 and 19 have disappeared: all the ones with employee_id IS NULL.

The mechanism is the one you saw in 02-03 with employee_id = NULL: NULL isn't equal to anything, not even to another NULL. The condition ON o.employee_id = e.id evaluates to NULL (which isn't TRUE) for those ten rows, so no partner saves them.

And here's the real damage: half of GreenStore's revenue has vanished from the report. If management asks "how many orders have we had this year?" and you answer with this query, your answer will be half the truth.

To sum up, an INNER JOIN loses rows for two reasons:

In both cases the answer is the same family of operators, and that's why lessons 03-03, 03-04 and 03-05 exist. The INNER JOIN isn't wrong: it simply answers the question "give me what matches", and sometimes the business question is a different one.

Nothing stops you putting extra conditions inside the ON, beyond the matching. These two queries ask for the same thing: the orders of Portuguese customers.

Identical results. And it's no coincidence: in an INNER JOIN they're always equivalent. Go back to the logical-order diagram from 03-01 and you'll see why. In an inner JOIN step 1d doesn't exist —no partnerless row is reintroduced—, so filtering during the matching or right afterwards produces the same set.

If they're equivalent, where should you put them? For readability:

And now the most important warning of this lesson: this equivalence is EXCLUSIVE to the INNER JOIN. In a LEFT JOIN, moving a condition from the ON to the WHERE changes the result, and it changes it in a way that looks like a data fault rather than a query fault. Lesson 03-03 demonstrates it with the same question written both ways and its two different results. Pick up the habit of separating matching from filtering right now: when you get to the LEFT JOIN, that habit will save you.

So far every JOIN in the lesson has preserved the starting table's number of rows. That happens when you join from the "many" side towards the "one" side (from products to categories, from orders to customers): each row finds exactly one partner.

The other way round, things change.

Three orders have produced eight rows. Order 1 appears three times (it has three lines), order 2 twice and order 3 three times. The header data —date, status, and also shipping_cost if we'd asked for it— repeats on every line.

The general rule:

When you join through a non-unique column on the right side, each row on the left is duplicated as many times as it finds partners.

This looks harmless while you're only looking at detail rows. It becomes dangerous in module 4, when you start adding things up. Picture this query:

Order 1's shipping cost is €4.95 once, but after the JOIN that figure appears on three rows. The sum would give €14.85 for that order alone: three times the real value. And there'll be no error, no warning, just a wrong number in a management report.

The same would happen when summing salary after joining employees with orders, or when counting customers after joining customers with order_lines.

Learn to spot it now: after every JOIN you add, ask yourself "how many rows on the right can there be for each row on the left?". If the answer is "more than one", any value from the left that you sum afterwards will be multiplied. In module 4 you'll see the techniques for avoiding it; for now it's enough to recognise the symptom.

A verification trick you can apply today: if your query's row count matches that of the detail table (47 for order_lines), you're at the right level of granularity to work with line amounts.

In an INNER JOIN, A JOIN B and B JOIN A return the same set of rows. The operation is commutative.

Both return the same 20 rows. The only things that can change are the column order if you use SELECT *, and the row order if you leave out the ORDER BY (the usual story since 02-01).

With three or more tables it's also associative: (A JOIN B) JOIN C is equivalent to A JOIN (B JOIN C), as long as the ON conditions are correctly placed.

Being commutative has two practical consequences:

1. Write the tables in the order the question is understood. If the report is "orders with their customer", start with orders. If it's "customers and their orders", start with customers. The result is the same and the query reads better.
2. The optimizer ignores you. PostgreSQL freely reorders INNER JOINs to pick the cheapest plan: it may start with the smallest table, or with the one carrying the most selective filter, regardless of how you wrote it. Your order is documentation for humans, not an instruction to the engine. (You'll see this in module 8.)

And a warning about what's coming. The LEFT JOIN is not commutative: A LEFT JOIN B and B LEFT JOIN A return different things. There the order in which you write the tables is part of the meaning, not of the style.

• Assuming the INNER JOIN preserves every row. It loses the ones that don't match on either side, without saying a word. Check the count against the starting table.
• Forgetting that a NULL FK never matches. The 10 web orders disappear when joining with employees. NULL = 4 isn't false: it's NULL, and NULL isn't TRUE.
• Calculating the amount with p.price instead of ol.unit_price. It applies today's price to a sale from a year ago. With line 1 the difference is €1.10; with a real catalogue, thousands of euros.
• Summing header values after joining with the detail. SUM(o.shipping_cost) after JOIN order_lines counts each order as many times as it has lines. It's the number 1 cause of inflated reports.
• Using DISTINCT as a patch. If you need DISTINCT to fix a JOIN, it's almost always the JOIN that's badly framed (02-04). Think about the granularity the question really wants.
• Believing the ON/WHERE equivalence is general. It only holds for INNER JOIN. In a LEFT JOIN it changes the result (03-03).
• Matching on columns of the right type but the wrong concept. ON ol.product_id = o.id raises no error, compares integers with integers, and returns meaningless data.
• Tip: validate each JOIN separately. Write FROM order_lines ol JOIN orders o ON ... with LIMIT 5 first, check it, and only then add the third table. Debugging a five-table query written in one go is torture.
• Tip: memorise GreenStore's three counts. 20 products, 20 orders, 47 lines. If a detail query doesn't return 47 rows, you know immediately that something's up.
• Tip: name your column aliases when joining tables with same-named columns. c.name AS customer and p.name AS product stop the result having two columns called name.

The product team wants to review the opinions received. Write a query that returns, for each review: its id, the name of the reviewed product, the full name of whoever wrote it, the rating and the date. Sort by review id.

Then answer: how many rows does it return, and why doesn't that number match the number of products in the catalogue?

Administration needs the detail of the returns. Write a query showing, for each return: its id, the date, the amount, the reason, the id and status of the returned order, and the full name of the customer affected.

State how many tables you needed, how many ON conditions and why.

Camille Dubois (customer 9) has called asking for the detail of everything she's bought. Using the canonical four-table query from section 4, get her complete history: order, date, order status, product, quantity and line amount.

Then answer these two questions:

1. How many rows does it return and how many orders do they represent?
2. If instead of an INNER JOIN between orders and customers you had joined orders with employees, would the same Camille orders appear? Reason it out by looking at the data.

Solution 1

12 rows, exactly those of reviews. We start from that table and its two foreign keys (product_id, customer_id) are mandatory and valid: each review finds one product and one customer, one and only one.

Why it doesn't match the 20 products: because the query goes from reviews to products, not the other way round. Only the 9 distinct products that have some review appear (the oil, the rice and the cream appear twice each). The 11 products with no review at all —among them the deodorant and the spirulina capsules— don't exist for this query. To see them you'd have to start from products and use a LEFT JOIN: which is exactly what the next lesson does.

Solution 2

Three tables and two ON conditions, following the N-1 rule from 03-01. Three are needed because the customer isn't reachable from returns in a single hop: returns only knows order_id, and it's orders that knows customer_id. The compulsory path is returns → orders → customers.

A detail that validates the data: return 2 is worth €34.02, exactly the amount of line 24 (2 units of face cream at €18.90 with a 10 % discount). That's no coincidence: it was that particular product from Camille's order that was returned.

Solution 3

1. 4 rows representing 2 orders: number 10 (delivered, two lines) and number 20 (pending, two lines). It's section 7's row multiplication in action: the number of rows is that of lines, not that of orders. If you wanted the number of Camille's orders, this query isn't the tool: you'd need module 4's COUNT(DISTINCT o.id).

2. No. Order 10 has employee_id = 4 (Óscar Peris Blasco) and would appear; order 20 has employee_id = 6 (Marc Estévez Roig) and would too. In this particular case both would indeed come out... but that's pure luck: both of Camille's orders have a sales rep assigned. Try it with Lucía Martínez Soler (customer 1), whose three orders —1, 5 and 15— are all web orders with employee_id IS NULL: an INNER JOIN with employees would make 100 % of her history disappear. That's the danger that motivates the next lesson.

The INNER JOIN holds no more secrets:

• JOIN and INNER JOIN are the same thing. The course uses JOIN in purely inner queries and an explicit INNER JOIN when several types live together.
• It returns only the intersection: the rows that find a partner on both sides. Anything that doesn't match is discarded silently.
• You know how to write chains of three and four tables and you have the canonical sales-detail query (order_lines + orders + customers + products, 47 rows), which you'll reuse in modules 4, 7 and 10.
• You know what gets lost: customers 13, 14 and 15 when joining customers with orders (12 customers out of 15), and the 10 web orders when joining orders with employees (10 rows out of 20). The two causes are the NULL FK and the absence of a child row.
• In an INNER JOIN, putting an extra condition in ON or in WHERE is equivalent; in a LEFT JOIN it won't be, and that's the next lesson's trap.
• Joining towards the "many" side multiplies rows: 20 orders become 47 once you add order_lines. It's the number one cause of the inflated sums you'll see in module 4.
• The INNER JOIN is commutative and associative: the order of the tables is a matter of readability, not of meaning. The LEFT JOIN won't be.

In the next lesson, LEFT JOIN, we'll recover everything we've thrown away here. Núria, Hugo and Inés will come back with their empty orders; the soy candles, the deodorant and the spirulina that nobody has ever bought will come back; the ten web orders with their sales rep at NULL will come back. You'll learn the anti-join pattern for directly answering "which customers have never bought?" and you'll see, with the same query written two ways, why placing a condition in ON or in WHERE stops being a matter of indifference.