ORDER BY with NULLS FIRST and NULLS LAST in PostgreSQL - Deep Dive

Overview - ORDER BY with NULLS FIRST and NULLS LAST

What is it?

ORDER BY is a command in databases that sorts the rows returned by a query. Sometimes, the data has missing values called NULLs. PostgreSQL lets you decide if these NULLs should appear at the start or the end of the sorted list using NULLS FIRST or NULLS LAST. This helps you control how incomplete or unknown data is shown when sorting.

Why it matters

Without controlling where NULLs appear, sorting results can be confusing or misleading. For example, if NULLs appear randomly in the middle, it might hide important data or make reports unclear. Being able to put NULLs first or last helps users see missing data clearly and keeps the order logical and useful.

Where it fits

Before learning this, you should understand basic SQL SELECT queries and simple ORDER BY sorting. After this, you can learn about more complex sorting techniques, filtering NULLs, and handling NULLs in calculations or joins.

Mental Model

Core Idea

ORDER BY with NULLS FIRST or NULLS LAST lets you control whether missing values appear at the start or end of sorted results.

Think of it like...

Imagine sorting a list of books by their publication year, but some books don’t have a year written. NULLS FIRST means putting those unknown-year books on the top shelf, while NULLS LAST means putting them on the bottom shelf.

Sorted list example:

Ascending order with NULLS FIRST:
┌───────────────┐
│ NULL          │
│ NULL          │
│ 2001          │
│ 2005          │
│ 2010          │
└───────────────┘

Ascending order with NULLS LAST:
┌───────────────┐
│ 2001          │
│ 2005          │
│ 2010          │
│ NULL          │
│ NULL          │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic ORDER BY Sorting

Concept: Learn how ORDER BY sorts query results in ascending or descending order.

In SQL, ORDER BY sorts rows by one or more columns. By default, ORDER BY sorts in ascending order (smallest to largest). You can add DESC to sort descending (largest to smallest). Example: SELECT name, age FROM people ORDER BY age ASC; This returns people sorted by age from youngest to oldest.

Result

Rows sorted by the chosen column in ascending order.

Understanding basic sorting is essential before handling special cases like NULL values.

2

Foundation

Understanding NULL Values in Sorting

Concept: Recognize that NULL means missing or unknown data and affects sorting behavior.

NULL is not a number or text; it means 'no value'. When sorting, databases must decide where to place NULLs. By default in PostgreSQL, NULLs sort as if they are larger than any value when sorting ascending, so they appear last.

Result

NULL values appear at the end in ascending order, and at the beginning in descending order by default.

Knowing how NULLs behave by default helps you understand why you might want to change their position.

3

Intermediate

Using NULLS FIRST to Prioritize NULLs

🤔Before reading on: do you think NULLS FIRST puts NULLs at the start or end of the sorted list? Commit to your answer.

Concept: NULLS FIRST forces NULL values to appear before all non-NULL values in the sorted output.

You can add NULLS FIRST after the ORDER BY column to make NULLs appear at the top. Example: SELECT name, age FROM people ORDER BY age ASC NULLS FIRST; This shows people with unknown age first, then the rest sorted ascending.

Result

Rows with NULL in the sorted column appear first, followed by rows with actual values in ascending order.

Understanding NULLS FIRST lets you highlight missing data by showing it before known data.

4

Intermediate

Using NULLS LAST to Deprioritize NULLs

🤔Before reading on: do you think NULLS LAST puts NULLs at the start or end of the sorted list? Commit to your answer.

Concept: NULLS LAST forces NULL values to appear after all non-NULL values in the sorted output.

You can add NULLS LAST after the ORDER BY column to make NULLs appear at the bottom. Example: SELECT name, age FROM people ORDER BY age DESC NULLS LAST; This shows people with known ages first in descending order, then those with unknown ages last.

Result

Rows with actual values appear first in descending order, followed by rows with NULL values.

Knowing NULLS LAST helps keep missing data out of the main sorted list, useful when you want to focus on known values.

5

Intermediate

Combining ORDER BY with Multiple Columns and NULLS

🤔Before reading on: if you sort by two columns, can you specify NULLS FIRST or LAST for each independently? Commit to your answer.

Concept: You can control NULL placement separately for each column in multi-column ORDER BY clauses.

Example: SELECT name, age, score FROM people ORDER BY age ASC NULLS LAST, score DESC NULLS FIRST; This sorts first by age ascending with NULLs last, then by score descending with NULLs first within the same age.

Result

Rows sorted by age with NULLs last, and within each age group, sorted by score with NULLs first.

Understanding this lets you fine-tune sorting when multiple columns and NULLs are involved.

6

Advanced

Default NULL Ordering Depends on Sort Direction

🤔Before reading on: does PostgreSQL put NULLs first or last by default when sorting descending? Commit to your answer.

Concept: PostgreSQL defaults NULLs to appear last in ascending order and first in descending order unless overridden.

When you write ORDER BY column ASC, NULLs appear last by default. When you write ORDER BY column DESC, NULLs appear first by default. Example: SELECT * FROM items ORDER BY price DESC; NULL prices appear before any actual price values.

Result

Default NULL placement changes with sort direction, which can surprise new users.

Knowing this default behavior prevents unexpected sorting results and helps you decide when to use NULLS FIRST or LAST explicitly.

7

Expert

Performance and Index Usage with NULLS FIRST/LAST

🤔Before reading on: do you think adding NULLS FIRST or LAST affects index usage in PostgreSQL? Commit to your answer.

Concept: Using NULLS FIRST or NULLS LAST can influence how PostgreSQL uses indexes for sorting, affecting query performance.

PostgreSQL can use indexes to speed up ORDER BY queries. However, if you specify NULLS FIRST or NULLS LAST explicitly, it might prevent the use of some indexes or require special index types (like btree with NULLS ordering). Creating indexes with NULLS FIRST or NULLS LAST can optimize queries: CREATE INDEX idx_price_nulls_last ON items(price NULLS LAST); This helps PostgreSQL quickly sort with the desired NULL placement.

Result

Explicit NULLS ordering can impact query speed and index effectiveness.

Understanding how NULLS FIRST/LAST interacts with indexes helps write faster queries and design better database schemas.

Under the Hood

PostgreSQL stores NULL as a special marker that means 'unknown' or 'missing'. When sorting, it treats NULLs as larger than any value for ascending order by default, so they appear last. For descending order, NULLs are treated as smaller, so they appear first. The NULLS FIRST and NULLS LAST keywords override this default by instructing the query planner to adjust the sort order internally, often by changing comparison logic or using special index ordering.

Why designed this way?

The default NULL ordering matches SQL standards and common expectations: NULLs are unknown, so they appear after known values in ascending order. Allowing explicit NULLS FIRST/LAST was added to give developers control for clearer data presentation. This design balances standard behavior with flexibility, avoiding confusion while supporting diverse use cases.

Sorting flow with NULLS control:

┌───────────────┐
│ Query with    │
│ ORDER BY col  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Check for     │
│ NULLS FIRST/  │
│ NULLS LAST    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Adjust sort   │
│ comparison or │
│ index usage   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Return sorted │
│ rows with     │
│ NULLs placed  │
│ as requested  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does ORDER BY always put NULLs at the end regardless of ASC or DESC? Commit to yes or no.

Common Belief: ORDER BY always places NULL values at the end of the sorted list.

Reality: In PostgreSQL, NULLs appear last in ascending order but first in descending order by default.

Why it matters: Assuming NULLs always appear last can cause confusion and incorrect data interpretation, especially when sorting descending.

Quick: Can you use NULLS FIRST or NULLS LAST without ORDER BY? Commit to yes or no.

Common Belief: NULLS FIRST and NULLS LAST can be used anywhere in SQL queries independently.

Reality: NULLS FIRST and NULLS LAST only work as part of ORDER BY clauses to control sorting of NULL values.

Why it matters: Trying to use NULLS FIRST/LAST outside ORDER BY causes syntax errors and misunderstanding of their purpose.

Quick: Does specifying NULLS FIRST or NULLS LAST always improve query performance? Commit to yes or no.

Common Belief: Adding NULLS FIRST or NULLS LAST makes queries faster because it optimizes sorting.

Reality: Explicit NULLS ordering can sometimes prevent index usage or require special indexes, potentially slowing queries if not planned carefully.

Why it matters: Blindly adding NULLS FIRST/LAST without considering indexes can degrade performance in large databases.

Quick: Does NULLS FIRST mean NULL is treated as the smallest value? Commit to yes or no.

Common Belief: NULLS FIRST means NULL is considered smaller than all other values in sorting.

Reality: NULLS FIRST means NULLs appear before non-NULLs, but NULL is not a value and comparisons treat it specially; it’s a sorting position, not a numeric value.

Why it matters: Misunderstanding NULL as a value can lead to wrong assumptions about query logic and results.

Expert Zone

1

PostgreSQL allows creating indexes with NULLS FIRST or NULLS LAST to optimize queries that use these clauses, which is often overlooked.

2

The interaction between NULLS ordering and collations (text sorting rules) can produce subtle differences in sort order for text columns with NULLs.

3

When using window functions or aggregates, NULLS FIRST/LAST in ORDER BY can affect frame boundaries and result sets in non-obvious ways.

When NOT to use

Avoid using NULLS FIRST or NULLS LAST when your application logic treats NULLs as equal to a specific value or when you want to filter out NULLs entirely. Instead, use WHERE clauses to exclude NULLs or COALESCE to replace NULLs with default values before sorting.

Production Patterns

In production, NULLS FIRST/LAST is often used in reporting queries to highlight missing data or push it to the end. Indexes are created with matching NULLS ordering to speed up these queries. Also, combined with pagination, explicit NULLS ordering ensures consistent and predictable result sets.

Connections

Three-Valued Logic (3VL) in SQL

ORDER BY NULLS FIRST/LAST builds on how SQL treats NULL as unknown in its logic.

Understanding that NULL is neither true nor false in SQL logic helps explain why sorting NULLs requires special handling.

Sorting Algorithms in Computer Science

ORDER BY with NULLS FIRST/LAST is a practical application of sorting algorithms that handle special sentinel values.

Knowing how sorting algorithms treat special values clarifies why databases need explicit NULL ordering controls.

User Interface Design

Controlling NULL placement in sorted data affects how users perceive and interact with incomplete information.

Recognizing the impact of NULL ordering on user experience guides better data presentation and decision-making.

Common Pitfalls

#1 Assuming NULLs always appear last in ascending order without specifying NULLS LAST.

Wrong approach:

SELECT * FROM employees ORDER BY salary ASC;

Correct approach:

SELECT * FROM employees ORDER BY salary ASC NULLS LAST;

Root cause:

Not knowing PostgreSQL defaults NULLs to last in ascending order but may differ in other databases or with descending order.

#2 Using NULLS FIRST without ORDER BY keyword.

Wrong approach:

SELECT * FROM products NULLS FIRST;

Correct approach:

SELECT * FROM products ORDER BY price NULLS FIRST;

Root cause:

Misunderstanding that NULLS FIRST is part of ORDER BY syntax, not a standalone clause.

#3 Expecting NULLS FIRST to treat NULL as zero or minimum value in calculations.

Wrong approach:

SELECT * FROM sales ORDER BY revenue ASC NULLS FIRST; -- expecting NULL = 0

Correct approach:

SELECT * FROM sales ORDER BY COALESCE(revenue, 0) ASC;

Root cause:

Confusing sorting position of NULLs with actual value substitution; NULLS FIRST only changes display order, not value.

Key Takeaways

ORDER BY with NULLS FIRST and NULLS LAST controls where missing values appear in sorted query results.

PostgreSQL defaults NULLs to last in ascending order and first in descending order unless overridden.

Explicitly specifying NULLS FIRST or NULLS LAST improves clarity and control over data presentation.

NULLS FIRST/LAST affects query performance and index usage, so use them thoughtfully in production.

Understanding NULL sorting behavior prevents common mistakes and helps write accurate, efficient queries.