ORDER BY with NULLS FIRST and NULLS LAST in PostgreSQL - Step-by-Step Execution

Concept Flow - ORDER BY with NULLS FIRST and NULLS LAST

Start with unsorted data

↓

Apply ORDER BY clause

↓

Check for NULL values

↓

Sort non-NULL values

↓

Return sorted result

The data is sorted by a column. NULL values are placed either at the start or end before sorting the non-NULL values.

Execution Sample

PostgreSQL

SELECT id, score
FROM players
ORDER BY score NULLS FIRST;

Sort players by score, placing NULL scores at the beginning.

Execution Table

Step	Row	score	NULL check	Placement	Order so far
1	Row 3	NULL	Is NULL	Place at start	[NULL]
2	Row 1	50	Not NULL	Place after NULLs	[NULL, 50]
3	Row 2	70	Not NULL	Place after NULLs	[NULL, 50, 70]
4	Row 4	90	Not NULL	Place after NULLs	[NULL, 50, 70, 90]
5	Row 5	NULL	Is NULL	Place at start	[NULL, NULL, 50, 70, 90]
6	End	-	-	-	Sorting complete

💡 All rows processed; NULLs placed first, then sorted non-NULL values.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Ordered List	[]	[NULL]	[NULL, 50]	[NULL, 50, 70]	[NULL, 50, 70, 90]	[NULL, NULL, 50, 70, 90]	[NULL, NULL, 50, 70, 90]

Key Moments - 2 Insights

Why do NULL values appear at the start when using NULLS FIRST?

Because the ORDER BY clause with NULLS FIRST explicitly tells the database to place all NULL values before any non-NULL values, as shown in execution_table rows 1 and 5.

Are NULL values sorted among themselves when using NULLS FIRST or NULLS LAST?

No, NULL values are treated as a group and placed either at the start or end without internal sorting, as seen in execution_table rows 1 and 5 where NULLs are simply added to the start.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the first NULL value placed?

A Step 3

B Step 2

C Step 1

D Step 5

💡 Hint

Check the 'NULL check' and 'Placement' columns in the first few rows.

According to variable_tracker, what is the ordered list after step 3?

A [50, 70]

B [NULL, 50, 70]

C [NULL, NULL, 50]

D [50, 70, 90]

💡 Hint

Look at the 'After 3' column for 'Ordered List' in variable_tracker.

If we change NULLS FIRST to NULLS LAST, where would the NULLs be placed?

A At the end

B In the middle

C At the start

D Randomly

💡 Hint

Refer to the concept_flow diagram showing NULLS FIRST vs NULLS LAST placement.

Concept Snapshot

ORDER BY sorts rows by a column.
NULLS FIRST puts NULL values before non-NULLs.
NULLS LAST puts NULL values after non-NULLs.
Non-NULL values are sorted normally.
Use: ORDER BY column NULLS FIRST|LAST;

Full Transcript

This visual shows how PostgreSQL sorts data using ORDER BY with NULLS FIRST. The database checks each row's value in the sorting column. If the value is NULL, it places that row at the start of the result when using NULLS FIRST. Non-NULL values come after, sorted normally. The execution table traces each row's placement step-by-step. The variable tracker shows how the ordered list grows. Key moments clarify why NULLs are grouped and not sorted among themselves. The quiz tests understanding of placement steps and effects of changing NULLS FIRST to NULLS LAST.