MIN and MAX functions in MySQL - Step-by-Step Execution

Concept Flow - MIN and MAX functions

Start with a column of values

↓

Apply MIN function

→Find smallest value

↓

Apply MAX function

→Find largest value

↓

Return the result

The MIN and MAX functions scan a column of values to find the smallest and largest values, then return those results.

Execution Sample

MySQL

SELECT MIN(age) AS Youngest, MAX(age) AS Oldest FROM people;

This query finds the youngest and oldest ages from the 'people' table.

Execution Table

Step	Action	Values Scanned	MIN Result	MAX Result
1	Start scanning 'age' column	[23, 45, 31, 19, 54]	NULL	NULL
2	Compare first value (23)	[23, 45, 31, 19, 54]	23	23
3	Compare second value (45)	[23, 45, 31, 19, 54]	23	45
4	Compare third value (31)	[23, 45, 31, 19, 54]	23	45
5	Compare fourth value (19)	[23, 45, 31, 19, 54]	19	45
6	Compare fifth value (54)	[23, 45, 31, 19, 54]	19	54
7	Return results	[23, 45, 31, 19, 54]	19	54

💡 All values scanned; MIN is 19 and MAX is 54

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
MIN	NULL	23	23	23	19	19	19
MAX	NULL	23	45	45	45	54	54

Key Moments - 3 Insights

Why does MIN start as NULL before scanning values?

MIN starts as NULL because no values have been checked yet. As shown in execution_table step 1, it updates to the first value scanned (23) at step 2.

Why does MAX update when a larger value is found?

MAX updates only when a scanned value is larger than the current MAX. For example, at step 3, 45 is larger than 23, so MAX changes to 45.

What happens if the column has NULL values?

MIN and MAX ignore NULL values and only consider actual numbers. This is why the example only shows numeric values in the scanned list.

Visual Quiz - 3 Questions

Look at the execution_table at step 4, what is the MIN value?

A 19

B 23

C 31

D 45

💡 Hint

Check the MIN Result column at step 4 in the execution_table.

At which step does the MAX value first change from 23 to a higher number?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look at the MAX Result column in execution_table rows for step 2 and 3.

If the 'age' column had a NULL value, how would MIN and MAX treat it?

A They would ignore NULL values

B They would treat NULL as zero

C They would return NULL as the result

D They would cause an error

💡 Hint

Refer to key_moments explanation about NULL values.

Concept Snapshot

MIN(column) returns the smallest value in a column.
MAX(column) returns the largest value in a column.
They ignore NULL values.
Use SELECT MIN(col), MAX(col) FROM table;
Results are single values summarizing the column.

Full Transcript

The MIN and MAX functions in SQL find the smallest and largest values in a column. They start with no value (NULL) and update as they scan each row. For example, scanning ages 23, 45, 31, 19, and 54, MIN updates from NULL to 23, then finally to 19, the smallest. MAX updates from NULL to 23, then to 45, and finally to 54, the largest. NULL values are ignored during this process. The final result is a single row showing the smallest and largest values found.