INNER JOIN Mechanics
Understand INNER JOIN as a filtered Cartesian product, write join conditions, and read EXPLAIN output for joins.
INNER JOIN Mechanics is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The INNER JOIN
INNER JOIN returns only rows that have a matching pair in BOTH tables, based on the ON condition.
Basic INNER JOIN
Pull each order with its user:
SELECT o.id, o.total, u.email
FROM orders o
INNER JOIN users u ON u.id = o.user_id;
-- INNER is the default — you can also write:
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id;Multiple Conditions in ON
The ON clause is a regular boolean expression:
SELECT *
FROM events e
JOIN sessions s
ON s.user_id = e.user_id
AND e.created_at BETWEEN s.start_at AND s.end_at;Aliases
Aliases shorten queries and avoid ambiguous column references:
SELECT o.id, u.email
FROM orders AS o -- AS is optional
JOIN users AS u ON u.id = o.user_id;Joining Three or More Tables
Chain joins by repeating the JOIN clause:
SELECT o.id, u.email, p.name AS product
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id;USING (column)
When the join column has the same name in both tables, you can write USING — it deduplicates the column in the output:
SELECT o.id, total, email
FROM orders
JOIN users USING (user_id); -- requires both tables to have user_idNATURAL JOIN
Joins on every column with the same name — beware: brittle when columns are added.
-- Avoid in production code:
SELECT * FROM orders NATURAL JOIN users;JOIN Conditions vs WHERE
For INNER JOIN, a condition in ON is logically equivalent to one in WHERE. Convention: filters that define the JOIN relationship go in ON; row filters go in WHERE.
SELECT * FROM orders o
JOIN users u ON u.id = o.user_id -- join condition
WHERE u.country = 'US'; -- row filterResult Multiplication
If a row in A matches three rows in B, the result has three rows. This is normal — but surprising when you forget. Aggregating? Use COUNT(*) carefully.
Self-Join Preview
You can join a table to itself by giving each instance a different alias:
SELECT e.full_name AS employee, m.full_name AS manager
FROM employees e
JOIN employees m ON m.id = e.manager_id;INNER JOIN Performance
Inner joins are fast when:
- The join column is indexed (especially the FK side)
- One table is small (hash join) or both are sorted (merge join)
- You filter early to keep intermediate row counts low
Recap
INNER JOIN keeps only matching pairs.
- JOIN = INNER JOIN by default
- ON expresses the join condition
- Aliases keep queries short
- Join columns should be indexed
Quick Check
What does orders INNER JOIN users ON users.id = orders.user_id return when an order has no matching user (orphan)?
Frequently asked questions
Is the “INNER JOIN Mechanics” lesson free?
Yes — the full text of “INNER JOIN Mechanics” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “INNER JOIN Mechanics”?
Understand INNER JOIN as a filtered Cartesian product, write join conditions, and read EXPLAIN output for joins. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “INNER JOIN Mechanics” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.