ORDER BY with NULL values behavior in SQL - Deep Dive

Overview - ORDER BY with NULL values behavior

What is it?

ORDER BY is a command in SQL that sorts the rows returned by a query. When sorting, some columns may have NULL values, which represent missing or unknown data. Different databases handle the position of these NULL values in the sorted list differently, either placing them at the beginning or the end.

Why it matters

Sorting data is essential for reports, analysis, and user interfaces. If NULL values are not handled clearly, the order can be confusing or misleading. Without understanding how NULLs behave in ORDER BY, you might get unexpected results, making data harder to trust or interpret.

Where it fits

Before learning ORDER BY with NULLs, you should understand basic SQL SELECT queries and the concept of NULL in databases. After this, you can learn about advanced sorting techniques, filtering NULLs, and database-specific NULL handling options.

Mental Model

Core Idea

When sorting data, NULL values act like unknowns and can be placed either at the start or end depending on the database or explicit instructions.

Think of it like...

Imagine sorting a list of books by their publication year, but some books have no year written on them (NULL). Depending on the librarian's rule, these unknown-year books might be placed on the shelf either at the very front or the very back.

Sorted List Example:

  ┌───────────────┐
  │ Sorted Values │
  ├───────────────┤
  │ NULL          │  <-- NULLs first (some DBs default)
  │ 1             │
  │ 2             │
  │ 3             │
  │ 4             │
  │ NULL          │  <-- NULLs last (other DBs or explicit)
  └───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic ORDER BY Sorting

Concept: How ORDER BY sorts rows by column values in ascending or descending order.

In SQL, ORDER BY sorts query results by one or more columns. By default, ORDER BY sorts in ascending order (smallest to largest). For example, SELECT * FROM table ORDER BY age; sorts rows by the age column from smallest to largest.

Result

Rows are returned sorted by the specified column in ascending order.

Understanding basic sorting is essential before considering how NULL values affect the order.

2

Foundation

Understanding NULL in SQL

Concept: What NULL means in databases and how it differs from zero or empty.

NULL means 'unknown' or 'missing' data. It is not zero, empty string, or any value. For example, a NULL age means the age is not recorded. NULLs behave differently in comparisons and sorting.

Result

Recognizing NULL as a special marker for missing data, not a value.

Knowing NULL is not a value helps explain why sorting treats NULLs differently.

3

Intermediate

Default NULL Sorting Behavior

🤔Before reading on: Do you think NULLs always appear at the start or end when sorting ascending? Commit to your answer.

Concept: How different SQL databases place NULLs by default when sorting.

Some databases like PostgreSQL place NULLs last when sorting ascending, while others like Oracle place NULLs first. This default behavior varies and can affect query results unexpectedly.

Result

The position of NULLs in sorted results depends on the database system's default.

Knowing that NULL sorting defaults vary prevents confusion when moving between databases.

4

Intermediate

Explicit NULLS FIRST and NULLS LAST

🤔Before reading on: Can you control where NULLs appear in ORDER BY results? Yes or No?

Concept: SQL syntax to explicitly specify NULLs position in ORDER BY.

Many SQL dialects support NULLS FIRST or NULLS LAST after ORDER BY to control NULL placement. For example, ORDER BY age ASC NULLS FIRST puts NULLs before all values, overriding defaults.

Result

You can control NULL placement explicitly in sorting results.

Explicit NULL placement gives precise control over sorting, improving clarity and consistency.

5

Intermediate

Sorting with Multiple Columns and NULLs

Concept: How NULLs behave when sorting by more than one column.

When sorting by multiple columns, NULLs in each column follow the same rules. For example, ORDER BY last_name ASC NULLS LAST, first_name ASC NULLS FIRST sorts by last name with NULLs last, then by first name with NULLs first.

Result

Complex sorting respects NULL placement rules per column.

Understanding multi-column NULL sorting helps build complex, predictable queries.

6

Advanced

Workarounds for Databases Without NULLS FIRST/LAST

🤔Before reading on: Can you think of a way to sort NULLs explicitly if your database lacks NULLS FIRST/LAST? Commit your idea.

Concept: Techniques to simulate NULLS FIRST or LAST in databases without explicit syntax.

You can use expressions like ORDER BY CASE WHEN column IS NULL THEN 0 ELSE 1 END, column ASC to force NULLs first or last. This uses a helper expression to sort NULLs separately.

Result

NULLs can be controlled even without built-in syntax.

Knowing workarounds ensures you can handle NULL sorting in any SQL environment.

7

Expert

Impact of NULL Sorting on Query Optimization

🤔Before reading on: Do you think NULL sorting affects how databases optimize queries? Yes or No?

Concept: How NULL sorting influences query plans and index usage internally.

Some databases optimize ORDER BY with NULLs by using indexes that store NULLs in specific positions. Explicit NULLS FIRST/LAST can affect whether indexes are used efficiently, impacting performance.

Result

NULL sorting choices can change query speed and resource use.

Understanding NULL sorting's effect on optimization helps write faster, scalable queries.

Under the Hood

Internally, databases store NULLs as special markers without a value. When sorting, the database engine compares rows and decides where to place NULLs based on its sorting rules or explicit instructions. Some engines treat NULL as smaller than any value, others as larger, affecting sort order. Indexes may store NULLs at start or end to speed up sorting.

Why designed this way?

NULLs represent unknown data, so they don't have a natural order. Different databases chose defaults based on historical standards or user expectations. Allowing explicit NULLS FIRST/LAST syntax was added later to give developers control and avoid ambiguity.

┌───────────────┐
│ Query Result  │
├───────────────┤
│ Row 1: Value  │
│ Row 2: NULL   │
│ Row 3: Value  │
└──────┬────────┘
       │
       ▼
┌───────────────────────────────┐
│ Sorting Engine                 │
│ ┌───────────────────────────┐ │
│ │ Compare rows, handle NULL │ │
│ │ according to rules        │ │
│ └─────────────┬─────────────┘ │
└───────────────┼───────────────┘
                ▼
      ┌─────────────────────┐
      │ Sorted Output       │
      │ NULLs first or last │
      └─────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think NULL is treated as the smallest value in all databases by default? Commit yes or no.

Common Belief: NULL is always treated as the smallest value when sorting ascending.

Reality: Different databases treat NULLs differently; some put NULLs first, others last by default.

Why it matters: Assuming NULLs are always smallest can cause unexpected order results and bugs when moving between databases.

Quick: Can you control NULL placement in ORDER BY in every SQL database by default? Commit yes or no.

Common Belief: You can always use NULLS FIRST or NULLS LAST in ORDER BY to control NULL placement.

Reality: Not all SQL databases support NULLS FIRST/LAST syntax; some require workarounds.

Why it matters: Expecting NULLS FIRST/LAST everywhere can lead to syntax errors or wrong sorting in some systems.

Quick: Does sorting NULLs last mean they are treated as the largest value? Commit yes or no.

Common Belief: NULLs last means NULL is treated as the largest value in sorting.

Reality: NULLs are not values and do not compare as larger or smaller; their position is a sorting rule, not a value comparison.

Why it matters: Misunderstanding NULLs as values can cause wrong assumptions about data and sorting logic.

Quick: Do NULLs behave the same in all sorting contexts, including multi-column sorts? Commit yes or no.

Common Belief: NULLs behave the same regardless of sorting by one or multiple columns.

Reality: NULL placement can be controlled separately per column in multi-column ORDER BY clauses.

Why it matters: Ignoring per-column NULL sorting can cause unexpected order in complex queries.

Expert Zone

1

Some databases optimize indexes by physically storing NULLs at the start or end, affecting query speed when sorting with NULLs.

2

Explicit NULLS FIRST/LAST can disable certain index optimizations, so use them only when necessary.

3

In some systems, NULL sorting behavior can be changed globally by configuration, impacting all queries.

When NOT to use

Avoid relying on default NULL sorting behavior in cross-database applications; instead, use explicit NULLS FIRST/LAST or workarounds. For databases without support, use CASE expressions. When performance is critical, test how NULL sorting affects index usage and query plans.

Production Patterns

In production, developers often use explicit NULLS FIRST/LAST to ensure consistent sorting across environments. For databases lacking support, CASE expressions are common. Monitoring query plans helps detect performance issues caused by NULL sorting. Complex reports use multi-column ORDER BY with careful NULL placement to meet business rules.

Connections

Three-valued Logic in SQL

Builds-on

Understanding how NULLs behave in sorting connects to how SQL treats NULLs in comparisons and conditions, which use three-valued logic (true, false, unknown).

Data Cleaning and Imputation

Related concept

Knowing how NULLs sort helps in data cleaning, where missing values might be replaced or handled differently to improve analysis and sorting.

Sorting Algorithms in Computer Science

Same pattern

The concept of placing unknown or special values at start or end during sorting is similar to handling sentinel values in sorting algorithms.

Common Pitfalls

#1 Assuming NULLs always appear at the end when sorting ascending.

Wrong approach:

SELECT * FROM employees ORDER BY salary ASC;

Correct approach:

SELECT * FROM employees ORDER BY salary ASC NULLS LAST;

Root cause:

Not knowing the database default NULL sorting behavior leads to unexpected NULL placement.

#2 Using NULLS FIRST/LAST in a database that does not support it.

Wrong approach:

SELECT * FROM products ORDER BY price ASC NULLS LAST;

Correct approach:

SELECT * FROM products ORDER BY CASE WHEN price IS NULL THEN 1 ELSE 0 END, price ASC;

Root cause:

Assuming all SQL dialects support NULLS FIRST/LAST syntax causes syntax errors.

#3 Treating NULL as a value in sorting comparisons.

Wrong approach:

Expecting NULL > 100 or NULL < 100 to be true in ORDER BY logic.

Correct approach:

Use explicit NULLS FIRST/LAST or CASE expressions to control NULL placement.

Root cause:

Misunderstanding that NULL means unknown, not a comparable value.

Key Takeaways

ORDER BY sorts query results but treats NULL values specially because they represent unknown data.

Different databases have different default positions for NULLs in sorted results, which can cause confusion.

You can explicitly control NULL placement using NULLS FIRST or NULLS LAST in many SQL dialects.

When NULLS FIRST/LAST is not supported, CASE expressions provide a reliable workaround.

Understanding NULL sorting behavior is crucial for writing clear, consistent, and performant SQL queries.