Subqueries vs JOINs comparison in MySQL - Visual Side-by-Side Comparison

Concept Flow - Subqueries vs JOINs comparison

Start Query

↓

Evaluate Subquery or JOIN

↓

Subquery: Execute inner query first

↓

Get subquery result

↓

Use result in outer query

↓

JOIN: Combine tables row by row

↓

Filter and select columns

↓

Return combined result

↓

End Query

This flow shows how a query with subqueries first runs the inner query and then uses its result, while a JOIN combines tables directly before filtering and returning results.

Execution Sample

MySQL

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

This query joins employees with their departments to list employee names with their department names.

Execution Table

Step	Action	Evaluation	Result
1	Start query execution	N/A	Ready to process tables
2	Read employees table row 1	e.name='Alice', e.department_id=1	Row data loaded
3	Find matching department with d.id=1	d.department_name='HR'	Match found
4	Combine employee and department data	('Alice', 'HR')	Row added to result
5	Read employees table row 2	e.name='Bob', e.department_id=2	Row data loaded
6	Find matching department with d.id=2	d.department_name='Sales'	Match found
7	Combine employee and department data	('Bob', 'Sales')	Row added to result
8	No more employee rows	End of employees table	Query complete

💡 All employee rows processed and matched with departments, query ends.

Variable Tracker

Variable	Start	After 1	After 2	Final
e.name	N/A	Alice	Bob	Bob
e.department_id	N/A	1	2	2
d.department_name	N/A	HR	Sales	Sales
Result Rows	Empty	('Alice', 'HR')	('Alice', 'HR'), ('Bob', 'Sales')	('Alice', 'HR'), ('Bob', 'Sales')

Key Moments - 2 Insights

Why does the JOIN combine rows from both tables instead of running one query inside another?

JOIN works by matching rows from both tables directly, combining them in one step (see execution_table rows 2-7), unlike subqueries which run separately first.

How does the query know which department matches each employee?

The ON condition e.department_id = d.id filters rows to match employee's department_id with department's id, shown in execution_table rows 3 and 6.

Visual Quiz - 3 Questions

Look at the execution_table, what is the combined row after processing the first employee?

A ('Alice', 'HR')

B ('Bob', 'Sales')

C ('Alice', 'Sales')

D ('Bob', 'HR')

💡 Hint

Check row 4 in execution_table where the first combined row is added.

At which step does the query finish processing all employee rows?

A Step 6

B Step 8

C Step 4

D Step 2

💡 Hint

Look at the exit_note and last row in execution_table.

If the JOIN condition was removed, what would happen to the result rows?

A Only employees with matching departments appear

B No rows would be returned

C All combinations of employees and departments appear (Cartesian product)

D Query would error out

💡 Hint

Without ON condition, JOIN returns every possible pair of rows from both tables.

Concept Snapshot

JOIN combines rows from two tables based on a condition.
Subqueries run inner queries first and use results in outer queries.
JOIN is often faster and clearer for related tables.
Subqueries can be simpler for filtering but may be slower.
Use JOIN to combine data side-by-side.
Use subqueries to filter or compute before main query.

Full Transcript

This visual execution compares subqueries and JOINs in MySQL. The flow shows that subqueries run inner queries first, then use results in the outer query, while JOINs combine tables row by row directly. The example query joins employees with departments using a matching condition. Step-by-step, each employee row is matched with a department row, combined, and added to the result. Variables track employee names, department ids, and combined results. Key moments clarify how JOIN matches rows and why it differs from subqueries. The quiz tests understanding of combined rows, query completion, and effects of removing the JOIN condition. The snapshot summarizes when to use JOINs or subqueries for combining or filtering data.