INNER JOIN step by step
35 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)An inner join returns only the pairs of rows that match: each row of one table glued to each row of the other table that satisfies the ON condition. It is the most common join, and in MySQL plain JOIN means INNER JOIN.
JOIN ... ON with matching keys
An inner join returns only the pairs of rows from two tables that satisfy the ON condition. A row with no partner on the other side is left out.
To list each appointment with its doctor, keep only the pairs whose keys match.
Example 1 · Appointments with their doctors
DROP TABLE IF EXISTS appointments, doctors;CREATE TABLE doctors ( doctor_id INT PRIMARY KEY, doctor VARCHAR(40), specialty VARCHAR(30));CREATE TABLE appointments ( appt_id INT PRIMARY KEY, patient VARCHAR(40), doctor_id INT, appt_time TIME);
INSERT INTO doctors VALUES (1, 'Dr. Mehta', 'Cardiology'), (2, 'Dr. Rao', 'Orthopaedics'), (3, 'Dr. Sen', 'Dermatology');INSERT INTO appointments VALUES (101, 'Ananya', 1, '10:00:00'), (102, 'Rohit', 2, '10:30:00'), (103, 'Kiran', 1, '11:00:00'), (104, 'Sana', NULL, '11:30:00');
-- Before: the two tablesSELECT * FROM doctors ORDER BY doctor_id;SELECT * FROM appointments ORDER BY appt_id;
-- After: each appointment paired with its doctorSELECT appointments.appt_id, appointments.patient, doctors.doctor, doctors.specialtyFROM appointmentsINNER JOIN doctors ON appointments.doctor_id = doctors.doctor_idORDER BY appointments.appt_id;+-----------+-----------+--------------+ | doctor_id | doctor | specialty | +-----------+-----------+--------------+ | 1 | Dr. Mehta | Cardiology | | 2 | Dr. Rao | Orthopaedics | | 3 | Dr. Sen | Dermatology | +-----------+-----------+--------------+ +---------+---------+-----------+-----------+ | appt_id | patient | doctor_id | appt_time | +---------+---------+-----------+-----------+ | 101 | Ananya | 1 | 10:00:00 | | 102 | Rohit | 2 | 10:30:00 | | 103 | Kiran | 1 | 11:00:00 | | 104 | Sana | NULL | 11:30:00 | +---------+---------+-----------+-----------+ +---------+---------+-----------+--------------+ | appt_id | patient | doctor | specialty | +---------+---------+-----------+--------------+ | 101 | Ananya | Dr. Mehta | Cardiology | | 102 | Rohit | Dr. Rao | Orthopaedics | | 103 | Kiran | Dr. Mehta | Cardiology | +---------+---------+-----------+--------------+
What this code does
- Appointments 101 and 103 both store
doctor_id = 1, so Dr. Mehta appears twice: one result row per matching pair, not one per doctor. - Sana (no doctor yet) and Dr. Sen (no appointments) are not in the result. The last section of this lesson explains why.
The usual condition is foreign key = primary key: appointments.doctor_id = doctors.doctor_id. Without a join, a hospital desk that wants "patient, doctor, specialty" would need two queries and a person matching ids by hand. The join does the matching for every row in one query. Read it in order: FROM the first table, INNER JOIN the second, ON how their rows pair up, then WHERE, ORDER BY and the column list work on the joined rows.
JOINandINNER JOINmean the same thing in MySQL.INNERjust makes the intent clear.- The order inside
ONdoes not matter:a.x = b.xandb.x = a.xpair the same rows. - When both columns have the same name,
USING (doctor_id)is a short form ofON a.doctor_id = b.doctor_id, and the column appears once inSELECT *.
Table aliases
A table alias is a short nickname given to a table right after its name in FROM or JOIN, such as products AS p. Once given, the alias replaces the table name in the whole query.
Full table names repeat in every column of a join. Give each table a short nickname instead.
Example 2 · Aliases p and c
SELECT p.product_id, p.name, p.price, c.name AS categoryFROM products AS pJOIN categories AS c ON p.category_id = c.category_idWHERE p.price >= 19999ORDER BY p.price DESC;+------------+-----------------------+----------+----------+ | product_id | name | price | category | +------------+-----------------------+----------+----------+ | 3 | iPhone 15 | 69900.00 | Mobiles | | 5 | HP Victus Gaming | 64990.00 | Laptops | | 4 | Lenovo IdeaPad Slim 3 | 42990.00 | Laptops | | 2 | Samsung Galaxy M35 | 19999.00 | Mobiles | +------------+-----------------------+----------+----------+
What this code does
products AS pandcategories AS clet every column be written asp.orc..FROM products pwithoutASworks the same.c.name AS categoryrenames the secondnamecolumn so the result has a clear heading.
Example 3 · Error: the full name after an alias
SELECT customers.name, o.order_idFROM customers cJOIN orders o ON o.customer_id = c.customer_idORDER BY o.order_id;ERROR 1054 (42S22): Unknown column 'customers.name' in 'field list'
What this code does
- After
FROM customers c, the table is known only ascin this query, socustomers.namerefers to nothing. - The fix is
SELECT c.name, o.order_id: use the alias everywhere once you have given one.
With aliases the query reads p.name, p.price, c.name instead of products.name, products.price, categories.name, which is shorter and easier to scan. Pick short, meaningful aliases: c for customers, o for orders, oi for order items. Writing customers.name after FROM customers c fails with ERROR 1054 (42S22): Unknown column 'customers.name' in 'field list'.
Choosing columns from both tables
A qualified column is a column written with its table or alias in front, such as p.name. It tells MySQL which table to read when both tables have a column with the same name.
Both products and categories have a name column, so MySQL cannot guess which one you mean. Put the table or alias in front.
Example 4 · Error: a column name both tables share
SELECT name, priceFROM products pJOIN categories c ON p.category_id = c.category_idWHERE price < 500;ERROR 1052 (23000): Column 'name' in field list is ambiguous
What this code does
priceexists only inproducts, so it is fine unqualified.nameexists in both tables, so MySQL stops with error 1052.- MySQL reports the first ambiguous column it meets; qualify it and run again to catch any other.
Example 5 · The fix: qualified and renamed columns
SELECT p.name AS product, c.name AS category, p.price, p.stockFROM products pJOIN categories c ON p.category_id = c.category_idWHERE c.name IN ('Books', 'Toys')ORDER BY p.price;+-----------------+----------+--------+-------+ | product | category | price | stock | +-----------------+----------+--------+-------+ | Rubik's Cube | Toys | 299.00 | 70 | | Atomic Habits | Books | 399.00 | 50 | | Clean Code | Books | 499.00 | 30 | | Hands-On Python | Books | 650.00 | 22 | +-----------------+----------+--------+-------+
What this code does
- Every column has its alias in front, so nothing is ambiguous, and
AS product/AS categorygive the twonamecolumns their own headings. - The
WHEREfilters on a column ofcategorieswhile the rows come fromproducts: after a join, both tables' columns are available everywhere.
You can mix columns from both tables in SELECT, WHERE and ORDER BY, and rename them with column aliases (p.name AS product). Without the qualifier, SELECT name fails with ERROR 1052 (23000): Column 'name' in field list is ambiguous: MySQL will not guess which one you mean. The same error appears for a shared key column such as dept_id, even in WHERE. A safe habit is to qualify every column in a join, even the ones that are not ambiguous today: if someone later adds a name column to the other table, your query still works. SELECT p.*, c.name takes all columns of one table plus one column of the other.
Fix the error
Fix the ambiguous column
Pick the line that makes the query run.
Both tables have a name column.
Error
ERROR 1052 (23000): Column 'name' in field list is ambiguous
name and name). Give them column aliases such as AS product and AS category so the result, and any application reading it, can tell them apart.Rows without a match disappear
An inner join keeps a row only if it found at least one partner. A row whose key matches nothing on the other side is silently left out of the result. No error, no warning: the row is just not there.
Example 6 · Counting the rows an inner join drops
SELECT COUNT(*) AS employees FROM employees;
SELECT COUNT(*) AS joined_rowsFROM employees eJOIN departments d ON e.dept_id = d.dept_id;
SELECT d.dept_name, COUNT(*) AS staffFROM employees eJOIN departments d ON e.dept_id = d.dept_idGROUP BY d.dept_nameORDER BY d.dept_name;+-----------+ | employees | +-----------+ | 13 | +-----------+ +-------------+ | joined_rows | +-------------+ | 12 | +-------------+ +-------------+-------+ | dept_name | staff | +-------------+-------+ | Engineering | 5 | | Finance | 2 | | HR | 2 | | Sales | 3 | +-------------+-------+
What this code does
- 13 employees went in but only 12 rows came out: Tanvi Kulkarni has
dept_idNULL, so she pairs with no department. - The staff count lists four departments. Research has no employees, so it never appears in an inner join and its count of 0 is missing.
Rows disappear in two ways. A child row may store NULL in its foreign key (an employee not yet given a department); NULL = 1 is not true, so it pairs with nothing. Or a parent row may have no children (a department with no employees yet), so no pair includes it.
Dropping unmatched rows is exactly right when you only want matched data, such as "orders with their customer". It is wrong when the question is about everyone ("every department and its staff count"). Compare row counts before and after a join to catch it; the next lesson's LEFT JOIN keeps the unmatched rows.
Predict the output
Predict how many rows survive
The employees table has 13 rows. How many rows come back?
NULL in the join column.Next step
Check your understanding
12 quick questions