JOIN with aggregate functions in MySQL - Step-by-Step Execution

Concept Flow - JOIN with aggregate functions

Start with two tables

↓

Perform JOIN on matching keys

↓

Group rows if needed

↓

Apply aggregate functions (SUM, COUNT, AVG, etc.)

↓

Return aggregated results with joined data

This flow shows how two tables are joined, then aggregate functions are applied on the joined data to summarize information.

Execution Sample

MySQL

SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

This query joins departments with employees and counts how many employees are in each department.

Execution Table

Step	Action	Tables Involved	Intermediate Result	Aggregate Result
1	Start with departments and employees tables	departments, employees	departments (4 rows), employees (6 rows)
2	Perform JOIN on department_id	departments JOIN employees	Joined rows (6 rows): each employee matched with their department
3	Group rows by department_name	Joined rows	Groups: Sales(2 rows), HR(1 row), IT(3 rows)
4	Apply COUNT(employee_id) per group	Grouped rows		Sales:2, HR:1, IT:3
5	Return final result with department_name and employee_count	Aggregated data		[{Sales,2}, {HR,1}, {IT,3}]
6	Query ends			All groups counted, query complete

💡 All joined rows processed and aggregated by department_name, query returns counts per department.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
departments	4 rows	4 rows	Grouped by name	Grouped by name	Grouped by name
employees	6 rows	6 rows	Grouped by department	Grouped by department	Grouped by department
joined_rows	N/A	6 rows	6 rows grouped	6 rows grouped	6 rows grouped
groups	N/A	N/A	3 groups	3 groups with counts	3 groups with counts
employee_count	N/A	N/A	N/A	Counts calculated	Counts calculated

Key Moments - 3 Insights

Why do we need to GROUP BY after JOIN when using aggregate functions?

Because aggregate functions like COUNT summarize data per group, GROUP BY tells SQL how to group joined rows before aggregation, as shown in execution_table step 3.

What happens if we forget to GROUP BY when using COUNT with JOIN?

The COUNT would apply to the entire joined result, giving one total count instead of counts per department. This is why step 3 grouping is essential.

Does JOIN increase the number of rows before aggregation?

Yes, JOIN combines matching rows from both tables, often increasing rows as seen in step 2 where 6 joined rows appear from 4 and 6 original rows.

Visual Quiz - 3 Questions

Look at the execution_table, how many rows are in the joined result after step 2?

A 4 rows

B 3 rows

C 6 rows

D 1 row

💡 Hint

Check the 'Intermediate Result' column at step 2 in the execution_table.

At which step does the query group rows by department_name?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look for the 'Group rows by department_name' action in the execution_table.

If we remove GROUP BY, what would happen to the employee_count?

A It would count all employees in one total

B It would count employees per department

C It would cause a syntax error

D It would return zero

💡 Hint

Refer to key_moments about the importance of GROUP BY with aggregate functions.

Concept Snapshot

JOIN with aggregate functions:
- JOIN combines rows from two tables on matching keys.
- GROUP BY groups joined rows by specified columns.
- Aggregate functions (COUNT, SUM, AVG) summarize each group.
- Without GROUP BY, aggregates apply to entire joined set.
- Use GROUP BY to get meaningful summaries per group.

Full Transcript

This visual execution shows how a SQL query joins two tables, groups the joined rows, and applies aggregate functions. First, the departments and employees tables are joined on department_id, producing 6 joined rows. Then, rows are grouped by department_name to prepare for aggregation. The COUNT function counts employees in each department group. Finally, the query returns the department names with their employee counts. Key points include the necessity of GROUP BY to get counts per department and how JOIN increases rows before aggregation.