NULL in DISTINCT, GROUP BY, and ORDER BY in SQL - Step-by-Step Execution

Concept Flow - NULL in DISTINCT, GROUP BY, and ORDER BY

Start Query

↓

Evaluate SELECT

↓

Apply DISTINCT

↓

Group Rows with GROUP BY

↓

Sort Rows with ORDER BY

↓

Return Result Set

The query processes rows by selecting columns, then removes duplicates with DISTINCT, groups rows with GROUP BY, and finally sorts them with ORDER BY, treating NULLs as equal in DISTINCT and GROUP BY.

Execution Sample

SQL

SELECT DISTINCT col
FROM table
GROUP BY col
ORDER BY col;

This query groups rows by 'col' and orders the result by 'col', showing how NULL values are handled in grouping and ordering.

Execution Table

Step	Action	Input Rows	Output Rows	Notes
1	Initial Rows	[1, NULL, 2, NULL, 3]	[1, NULL, 2, NULL, 3]	Original data with NULLs
2	Apply DISTINCT	[1, NULL, 2, NULL, 3]	[1, NULL, 2, 3]	NULL treated as equal, duplicates removed
3	GROUP BY col	[1, NULL, 2, 3]	[1, NULL, 2, 3]	Rows grouped; NULLs form one group
4	ORDER BY col	[1, NULL, 2, 3]	[NULL, 1, 2, 3]	NULLs sorted first by default
5	Return Result	[NULL, 1, 2, 3]	[NULL, 1, 2, 3]	Final sorted grouped distinct rows

💡 All steps complete; NULLs treated as equal in DISTINCT and GROUP BY, sorted first in ORDER BY

Variable Tracker

Variable	Start	After DISTINCT	After GROUP BY	After ORDER BY	Final
Rows	[1, NULL, 2, NULL, 3]	[1, NULL, 2, 3]	[1, NULL, 2, 3]	[NULL, 1, 2, 3]	[NULL, 1, 2, 3]

Key Moments - 3 Insights

Why does DISTINCT treat multiple NULLs as one value?

Because in SQL, NULLs are considered equal for DISTINCT, so duplicates of NULL collapse into one row as shown in step 2 of the execution_table.

How does GROUP BY handle NULL values?

GROUP BY treats all NULLs as belonging to the same group, so multiple NULL rows form one group, as seen in step 3.

Where do NULLs appear when using ORDER BY without specifying NULLS FIRST or LAST?

By default, NULLs appear first in ORDER BY ascending, as shown in step 4 where NULL is placed before 1.

Visual Quiz - 3 Questions

Look at the execution_table, after applying DISTINCT, how many NULL values remain?

A One NULL value

B Two NULL values

C No NULL values

D Three NULL values

💡 Hint

Check the 'After DISTINCT' column in the 'Rows' variable in variable_tracker and step 2 in execution_table.

At which step does the query group all NULL values into one group?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'GROUP BY col' step in execution_table where grouping happens.

If ORDER BY was changed to ORDER BY col DESC, where would NULLs appear?

A At the beginning

B In the middle

C At the end

D NULLs would be removed

💡 Hint

By default, NULLs sort last in descending order; see step 4 notes about ORDER BY behavior.

Concept Snapshot

SQL treats NULLs as equal in DISTINCT and GROUP BY, so multiple NULLs collapse into one group or distinct value.
ORDER BY sorts NULLs first by default in ascending order and last in descending order.
This affects how query results with NULLs appear when using these clauses.
Remember: NULL is not a value but treated specially in these operations.

Full Transcript

This visual execution shows how SQL handles NULL values in DISTINCT, GROUP BY, and ORDER BY clauses. Initially, the data contains multiple NULLs. When DISTINCT is applied, all NULLs are treated as one, removing duplicates. GROUP BY then groups all NULLs into a single group. Finally, ORDER BY sorts the results, placing NULLs first by default in ascending order. This step-by-step trace helps understand that NULLs are treated as equal in DISTINCT and GROUP BY, and their position in ORDER BY depends on sorting direction.