INNER JOIN

Overview

INNER JOIN combines rows from two tables where a condition is true — typically matching a foreign key in one table to a primary key in another. (Note: writing just `JOIN` is shorthand for `INNER JOIN` in PostgreSQL). Rows that have no match in the other table are silently dropped from the result. Think of it as a filter across two tables: start with every possible pair of rows (the Cartesian product), then keep only the pairs where the ON condition is true. Against the shared dataset: SELECT c.name, o.id AS order_id FROM customers c INNER JOIN orders o ON o.customer_id = c.id ORDER BY c.name, o.id; This returns 8 rows — one for every order matched to its customer. Customers Greg and Hana appear nowhere because they have no orders; the join silently will exclude them. Mental model: INNER JOIN = 'give me only the rows that exist in BOTH tables'.

INNER JOIN is the most common join type. Almost every multi-table query starts here — listing orders with customer names, showing line items with product details, connecting any two related tables. Understanding that unmatched rows vanish is the key insight that separates it from outer joins.

Where used: order detail pages joining orders to products, analytics dashboards combining customers and orders, API endpoints returning enriched records

Why learn this

• Querying across two or more tables is the single most common SQL task — you cannot build any realistic report without joins
• Understanding which rows INNER JOIN drops prepares you to choose LEFT JOIN when you need to keep unmatched rows

Query walkthrough

SELECT c.name, p.name AS product, oi.quantity
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
WHERE c.name = 'Alice'
ORDER BY o.id, p.name;

Focus: Watch four tables chain together: customers → orders → order_items → products. Each INNER JOIN narrows the result to only matching rows, and the WHERE further filters to Alice's rows only.

Common mistakes

• Forgetting the ON clause: In Postgres and Standard SQL, omitting ON (or writing JOIN without a condition) produces a syntax error. If you actually want a Cartesian product (every row paired with every other row), you must explicitly use CROSS JOIN.
• Joining on the wrong column: Writing ON c.id = o.id instead of ON c.id = o.customer_id joins customer IDs to order IDs, which happen to match by coincidence in small datasets but produces completely wrong results. Check which column is the foreign key.
• Not realizing INNER JOIN drops unmatched rows: If a customer has no orders, INNER JOIN silently removes them. This is the correct behavior when you want 'only customers who ordered', but a bug when you want 'all customers, with their orders if any' — use LEFT JOIN for that.

Glossary

foreign

A column in one table that connects to the primary key in another table, creating a relationship between the two.

exclude

To deliberately leave out or remove items from a result, such as ignoring rows that do not have a match.

insight

A deep understanding or realization about how a specific concept or system works.

Recall questions

• What happens to a row in table A when it has no matching row in table B during an INNER JOIN?
• What is the result of an INNER JOIN without an ON clause?
• Is 'JOIN' the same as 'INNER JOIN' in PostgreSQL?

Understanding checks

Which customer names and statuses does this return?

Alice, paid Alice, paid Bob, paid Carlos, paid Emma, paid Farah, paid

There are 8 orders total; INNER JOIN matches all 8 to their customers. The WHERE clause then keeps only the 6 with status = 'paid', dropping Bob's pending order (order 3) and Diana's cancelled order (order 5).

What names and counts appear? Do Greg and Hana appear?

Alice, 2 Bob, 2 Carlos, 1 Diana, 1 Emma, 1 Farah, 1 Greg and Hana do NOT appear.

INNER JOIN drops customers with no matching orders. Greg and Hana have zero orders, so they never enter the joined row set and cannot be counted. To include them with a count of 0 you would need a LEFT JOIN with COUNT(o.id).

Practice tasks

Add product prices to the order items

This query lists order items with their product names. Modify it to also show each product's price by adding the missing column from the products table. Keep the existing JOIN and ordering.

Challenge

Total spend per customer

Write a query that shows each customer's name and their total spend (quantity × price across all their order items). Only include customers who have orders. Order by total spend descending, then by name ascending.

Continue learning

Previous: SELECT, FROM & Columns

Previous: WHERE & Operators

Next: LEFT / RIGHT JOIN

Next: FULL OUTER & CROSS JOIN

Next: Self Join

Return to SQL Roadmap