INNER JOIN in MySQL - Step-by-Step Execution

Concept Flow - INNER JOIN

Start with Table A

↓

Start with Table B

↓

Compare rows from A and B

↓

Match rows where join condition is true

↓

Combine matched rows into result

↓

Return combined rows only

INNER JOIN takes two tables and returns only the rows where the join condition matches in both tables.

Execution Sample

MySQL

SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.id;

This query returns employee names with their department names, only where the department ID matches in both tables.

Execution Table

Step	employees row	departments row	Join Condition (employees.dept_id = departments.id)	Action	Output row
1	{id:1, name:'Alice', dept_id:10}	{id:10, dept_name:'HR'}	10 = 10 (True)	Combine rows	{name:'Alice', dept_name:'HR'}
2	{id:1, name:'Alice', dept_id:10}	{id:20, dept_name:'Sales'}	10 = 20 (False)	Skip	No output
3	{id:2, name:'Bob', dept_id:20}	{id:10, dept_name:'HR'}	20 = 10 (False)	Skip	No output
4	{id:2, name:'Bob', dept_id:20}	{id:20, dept_name:'Sales'}	20 = 20 (True)	Combine rows	{name:'Bob', dept_name:'Sales'}
5	{id:3, name:'Carol', dept_id:30}	{id:10, dept_name:'HR'}	30 = 10 (False)	Skip	No output
6	{id:3, name:'Carol', dept_id:30}	{id:20, dept_name:'Sales'}	30 = 20 (False)	Skip	No output
7	No more employees rows	N/A	N/A	End	Final output rows returned

💡 All rows from employees checked against all departments; only matching dept_id rows combined and returned.

Variable Tracker

Variable	Start	After Step 1	After Step 4	After Step 7 (End)
Current employees row	None	{id:1, name:'Alice', dept_id:10}	{id:2, name:'Bob', dept_id:20}	None
Current departments row	None	{id:10, dept_name:'HR'}	{id:20, dept_name:'Sales'}	None
Output rows	[]	[{name:'Alice', dept_name:'HR'}]	[{name:'Alice', dept_name:'HR'}, {name:'Bob', dept_name:'Sales'}]	[{name:'Alice', dept_name:'HR'}, {name:'Bob', dept_name:'Sales'}]

Key Moments - 2 Insights

Why are some rows from employees not in the output?

Rows from employees without a matching dept_id in departments are skipped, as shown in steps 5 and 6 where Carol's dept_id 30 does not match any department id.

Does INNER JOIN include rows with no matching partner in the other table?

No, INNER JOIN only returns rows where the join condition is true for both tables, as seen in the execution table where only matching dept_id rows produce output.

Visual Quiz - 3 Questions

Look at the execution table, what is the output row at Step 4?

A {name:'Alice', dept_name:'HR'}

B {name:'Bob', dept_name:'Sales'}

C No output

D {name:'Carol', dept_name:'HR'}

💡 Hint

Check the 'Output row' column at Step 4 in the execution_table.

At which step does the join condition first become false?

A Step 1

B Step 4

C Step 2

D Step 7

💡 Hint

Look at the 'Join Condition' column in the execution_table for the first 'False' value.

If a new department with id 30 is added, how would the output change?

A Carol's row would appear in the output

B No change in output

C All employees would be excluded

D Only departments with id 10 and 20 would appear

💡 Hint

Refer to the variable_tracker and execution_table where Carol's dept_id 30 had no match before.

Concept Snapshot

INNER JOIN syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Returns rows only where join condition matches in both tables.
Rows without matches are excluded.

Full Transcript

INNER JOIN combines rows from two tables where the join condition is true. It compares each row from the first table with each row from the second table. Only rows with matching values in the join columns are combined and returned. Rows without a match in the other table are skipped. For example, employees are joined with departments on dept_id. Only employees with a matching department id appear in the result. This is shown step-by-step in the execution table, where each employee row is checked against department rows, and only matching pairs produce output rows.

Practice

1. What does an INNER JOIN do in MySQL?

easy

A. Combines rows from two tables where matching values exist in both tables.

B. Returns all rows from the first table regardless of matches.

C. Returns all rows from the second table regardless of matches.

D. Combines rows even if there is no matching value in either table.

Solution

1. Step 1: Understand INNER JOIN purpose

   INNER JOIN returns rows only when there is a match in both tables based on the join condition.

2. Step 2: Compare options with definition

   Combines rows from two tables where matching values exist in both tables. correctly describes this behavior. Options A, C, and D describe other join types or incorrect behavior.

3. Final Answer:

   Combines rows from two tables where matching values exist in both tables. -> Option A

4. Quick Check:

   INNER JOIN = matching rows only [OK]

Hint: INNER JOIN returns only rows with matching keys in both tables [OK]

Common Mistakes:

• Confusing INNER JOIN with LEFT or RIGHT JOIN
• Thinking INNER JOIN returns unmatched rows
• Assuming INNER JOIN returns all rows from one table

2. Which of the following is the correct syntax for an INNER JOIN in MySQL?

easy

A. SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

B. SELECT * FROM table1 INNER JOIN table2 WHERE table1.id = table2.id;

C. SELECT * FROM table1 INNER JOIN table2 USING table1.id;

D. SELECT * FROM table1 INNER JOIN table2 ON table1.id == table2.id;

Solution

1. Step 1: Review INNER JOIN syntax

   The correct syntax uses INNER JOIN with ON and a single equals sign (=) for comparison.

2. Step 2: Analyze each option

   SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; uses correct syntax. SELECT * FROM table1 INNER JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON, which is incorrect for JOIN conditions. SELECT * FROM table1 INNER JOIN table2 USING table1.id; uses USING with table prefix which is invalid; USING requires unqualified column names. SELECT * FROM table1 INNER JOIN table2 ON table1.id == table2.id; uses double equals (==) which is invalid in SQL.

3. Final Answer:

   SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   INNER JOIN syntax uses ON with = [OK]

Hint: Use ON with single = for INNER JOIN conditions [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Using double equals (==) instead of single equals (=)
• Confusing USING syntax with ON syntax

3. Given two tables:
Employees(id, name, dept_id)
Departments(dept_id, dept_name)
What will this query return?

SELECT Employees.name, Departments.dept_name FROM Employees INNER JOIN Departments ON Employees.dept_id = Departments.dept_id;

medium

A. All departments with employees, even if no employees exist.

B. All employees, even if they have no department.

C. An error because dept_id is ambiguous.

D. All employees with their department names, only if department exists.

Solution

1. Step 1: Understand INNER JOIN effect on these tables

   The INNER JOIN returns rows where Employees.dept_id matches Departments.dept_id, so only employees with existing departments appear.

2. Step 2: Analyze query output

   The query selects employee names and their department names only for matching dept_id pairs. Employees without a department are excluded.

3. Final Answer:

   All employees with their department names, only if department exists. -> Option D

4. Quick Check:

   INNER JOIN filters to matching dept_id rows [OK]

Hint: INNER JOIN shows only matching rows from both tables [OK]

Common Mistakes:

• Thinking all employees appear even without departments
• Confusing INNER JOIN with LEFT JOIN
• Assuming ambiguous column error without aliasing

4. Identify the error in this query:

SELECT e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.dept_id = d.id;

medium

A. Missing WHERE clause for join condition.

B. Column d.id does not exist; should be d.dept_id.

C. Alias 'e' is not allowed in INNER JOIN.

D. Using INNER JOIN without SELECT * causes error.

Solution

1. Step 1: Check join condition columns

   The join condition uses d.id, but Departments table has dept_id column, not id.

2. Step 2: Identify correct column name

   It should be d.dept_id to match Employees.dept_id. Other options are incorrect because aliasing is allowed, WHERE is not needed for JOIN, and SELECT * is not mandatory.

3. Final Answer:

   Column d.id does not exist; should be d.dept_id. -> Option B

4. Quick Check:

   Join columns must exist and match [OK]

Hint: Check column names in JOIN condition carefully [OK]

Common Mistakes:

• Using wrong column names in ON clause
• Thinking WHERE is required for JOIN
• Believing aliases are not allowed

5. You have two tables:
Orders(order_id, customer_id, amount)
Customers(customer_id, name)
You want to find customers who placed orders over 100 dollars. Which query correctly uses INNER JOIN to get customer names and order amounts over 100?

hard

A. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount < 100;

B. SELECT Customers.name, Orders.amount FROM Customers LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100;

C. SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100;

D. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount >= 100;

Solution

1. Step 1: Understand the requirement

   We want customers with orders over 100 dollars, so filter Orders.amount > 100.

2. Step 2: Check INNER JOIN usage and filter

   SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; correctly joins Customers and Orders on customer_id and filters orders with amount > 100. SELECT Customers.name, Orders.amount FROM Customers LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; uses LEFT JOIN instead of INNER JOIN. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount >= 100; uses >= 100 which includes 100, not strictly over 100. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount < 100; filters for amount < 100, opposite of requirement.

3. Final Answer:

   SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; -> Option C

4. Quick Check:

   INNER JOIN + filter amount > 100 = SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; [OK]

Hint: Use INNER JOIN with WHERE to filter matching rows [OK]

Common Mistakes:

• Using LEFT JOIN instead of INNER JOIN
• Using wrong comparison operator in WHERE
• Filtering on wrong table or column