ORDER BY with NULL values behavior in SQL - Step-by-Step Execution

Concept Flow - ORDER BY with NULL values behavior

Start with unsorted data

↓

Apply ORDER BY clause

↓

Check for NULL values

↓

If NULLs, decide position

↓

NULLS FIRST or NULLS LAST

↓

Sort NULLs accordingly

↓

Sort non-NULL values

↓

Return sorted result

The ORDER BY clause sorts data, handling NULL values by placing them either first or last depending on the NULLS FIRST or NULLS LAST option.

Execution Sample

SQL

SELECT name, score FROM players ORDER BY score NULLS LAST;

This query sorts players by score, placing NULL scores at the end.

Execution Table

Step	Row	score value	NULL check	Action	Position in sorted list
1	Alice	85	Not NULL	Consider for sorting	To be placed among non-NULLs
2	Bob	NULL	NULL	Place according to NULLS LAST	At the end
3	Charlie	92	Not NULL	Consider for sorting	To be placed among non-NULLs
4	Diana	70	Not NULL	Consider for sorting	To be placed among non-NULLs
5	Eve	NULL	NULL	Place according to NULLS LAST	At the end
6	Sorting non-NULL scores	-	-	Sort 70, 85, 92 ascending	Diana, Alice, Charlie
7	Final sorted list	-	-	Combine sorted non-NULLs + NULLs last	Diana, Alice, Charlie, Bob, Eve

💡 All rows processed; NULLs placed last as specified; sorting complete.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
score	unsorted	85 (Alice)	NULL (Bob)	92 (Charlie)	70 (Diana)	sorted: 70, 85, 92, NULL, NULL
NULL position	unknown	checked	checked	checked	checked	NULLS LAST

Key Moments - 2 Insights

Why do NULL values appear at the end of the sorted list?

Because the query uses NULLS LAST, the execution_table rows 2, 5, and 7 show NULL values are placed after all non-NULL values.

Are NULL values considered when sorting non-NULL values?

No, as shown in execution_table row 6, only non-NULL scores are sorted; NULLs are handled separately.

Visual Quiz - 3 Questions

Look at the execution_table, at which step are NULL values assigned their position?

A Step 2 and Step 5

B Step 1

C Step 6

D Step 7

💡 Hint

Check the 'NULL check' and 'Action' columns in rows 2 and 5.

According to variable_tracker, what is the final order of scores?

A NULL, 70, 85, 92, NULL

B 85, 70, 92, NULL, NULL

C 70, 85, 92, NULL, NULL

D 92, 85, 70, NULL, NULL

💡 Hint

Look at the 'score' row in variable_tracker under 'Final'.

If we change NULLS LAST to NULLS FIRST, where would NULL values appear?

A At the end of the list

B At the beginning of the list

C In the middle of the list

D NULL values would be ignored

💡 Hint

Refer to concept_flow where NULLS FIRST places NULLs before non-NULLs.

Concept Snapshot

ORDER BY sorts rows by column values.
NULL values can be placed first or last using NULLS FIRST or NULLS LAST.
Without specifying, NULL placement depends on the database.
Use ORDER BY column NULLS FIRST to put NULLs at the start.
Use ORDER BY column NULLS LAST to put NULLs at the end.

Full Transcript

This visual execution shows how the ORDER BY clause sorts data with NULL values. The query orders players by their score, placing NULL scores last as specified by NULLS LAST. Each row is checked: non-NULL scores are sorted ascending, while NULL scores are assigned positions at the end. The variable tracker shows the score values before and after sorting. Key moments clarify why NULLs appear last and how they are excluded from the numeric sort. The quiz tests understanding of NULL placement steps and final order. Remember, NULLS FIRST would place NULLs at the beginning instead.