Order By - Learning Module

Loading content...

0/241

NULL Ordering: Mastering the Unknown in Sorted Results

The Challenge of Ordering the Unknown

NULL presents a fundamental challenge to ordering operations. By definition, NULL represents unknown or missing data—it's not a value, but the absence of a value. How do you compare unknown quantities? Where should rows with missing values appear in a sorted list?

These questions don't have mathematically "correct" answers—they require convention. Unfortunately, SQL implementations have historically chosen different conventions, creating a cross-platform compatibility challenge.

This page comprehensively explores:

What NULL means in the context of comparison and ordering
Default behaviors across major database systems
NULLS FIRST/LAST — SQL standard syntax for explicit control
Workarounds for databases lacking native NULL ordering control
Performance implications of NULL-aware ordering
Best practices for predictable, portable NULL handling

Cross-Platform Alert

NULL ordering defaults differ significantly between databases. Code that works correctly in PostgreSQL may produce unexpected ordering in MySQL or SQL Server. Always specify NULL behavior explicitly if it matters to your application.

What You Will Master

By the end of this page, you will understand why NULL complicates ordering, know the default behavior of every major database, master NULLS FIRST/LAST syntax, implement workarounds for unsupported systems, and write portable NULL-aware ORDER BY clauses.

Understanding NULL in Comparison Operations

Three-Valued Logic: TRUE, FALSE, and UNKNOWN

SQL uses three-valued logic (3VL) rather than simple TRUE/FALSE. Any comparison involving NULL yields UNKNOWN, not TRUE or FALSE:

Expression	Result
5 > 3	TRUE
5 < 3	FALSE
5 > NULL	UNKNOWN
NULL > 3	UNKNOWN
NULL = NULL	UNKNOWN
NULL <> NULL	UNKNOWN

This creates a fundamental problem for sorting: if we can't determine whether NULL is greater than, less than, or equal to any value, where should NULL rows appear?

null_comparison_demo.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Demonstrate NULL comparison behavior
SELECT 
    CASE WHEN NULL > 1 THEN 'TRUE' 
         WHEN NOT (NULL > 1) THEN 'FALSE' 
         ELSE 'UNKNOWN' 
    END AS result;
-- Result: 'UNKNOWN'
 
SELECT 
    CASE WHEN NULL = NULL THEN 'TRUE' 
         WHEN NOT (NULL = NULL) THEN 'FALSE' 
         ELSE 'UNKNOWN' 
    END AS result;
-- Result: 'UNKNOWN' (NULL is not equal to itself!)
 
-- This is why you must use IS NULL, not = NULL
SELECT * FROM employees WHERE manager_id IS NULL;      -- Correct
SELECT * FROM employees WHERE manager_id = NULL;       -- Always returns 0 rows!

The Ordering Convention Problem

Since NULL comparisons yield UNKNOWN, databases must make a design decision about NULL placement in sorted results. Two approaches exist:

NULL is "smallest" — NULL rows appear first in ASC order, last in DESC order
NULL is "largest" — NULL rows appear last in ASC order, first in DESC order

Neither is more "correct"—they represent different design philosophies:

NULL smallest: "Missing data has minimal value/priority"
NULL largest: "Missing data should be noticed/handled last"

No Universal Standard

The SQL standard doesn't mandate which default to use. It only requires that databases provide a consistent convention and offer NULLS FIRST/LAST for explicit control. Unfortunately, not all databases implement NULLS FIRST/LAST.

Default NULL Ordering by Database System

Understanding each database's default is essential for portable code and debugging unexpected ordering.

Default NULL Ordering by Database
Database	NULL Position in ASC	NULL Position in DESC	NULLS FIRST/LAST Support
PostgreSQL	Last (NULL largest)	First (NULL largest)	✓ Full support
Oracle	Last (NULL largest)	First (NULL largest)	✓ Full support
SQL Server	First (NULL smallest)	Last (NULL smallest)	✗ Not supported
MySQL	First (NULL smallest)	Last (NULL smallest)	✗ Not supported (pre-8.0.21)
MySQL 8.0.21+	First (NULL smallest)	Last (NULL smallest)	✓ Supported via parser
SQLite	First (NULL smallest)	Last (NULL smallest)	✓ Full support (3.30.0+)
DB2	Last (NULL largest)	First (NULL largest)	✓ Full support

Demonstration of Default Differences

default_differences.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Sample data
-- products: (1, 'Widget', 29.99), (2, 'Gadget', NULL), (3, 'Thing', 19.99)
 
-- PostgreSQL / Oracle: NULL is largest
SELECT name, price FROM products ORDER BY price ASC;
-- Result: Thing (19.99), Widget (29.99), Gadget (NULL)
-- NULL appears LAST in ascending order
 
SELECT name, price FROM products ORDER BY price DESC;
-- Result: Gadget (NULL), Widget (29.99), Thing (19.99)
-- NULL appears FIRST in descending order
 
-- MySQL / SQL Server: NULL is smallest
SELECT name, price FROM products ORDER BY price ASC;
-- Result: Gadget (NULL), Thing (19.99), Widget (29.99)
-- NULL appears FIRST in ascending order
 
SELECT name, price FROM products ORDER BY price DESC;
-- Result: Widget (29.99), Thing (19.99), Gadget (NULL)
-- NULL appears LAST in descending order

Migration and Compatibility Risk

If your application moves from PostgreSQL to MySQL (or vice versa), NULL ordering will flip! Any UI relying on specific NULL placement will break. Always test sorting with NULL values when changing database platforms.

NULLS FIRST and NULLS LAST: Explicit Control

The SQL standard provides NULLS FIRST and NULLS LAST keywords to explicitly specify where NULL values should appear in sorted results, overriding any default behavior.

Syntax

ORDER BY column [ASC|DESC] [NULLS {FIRST | LAST}]

Each column in ORDER BY can independently specify its NULL behavior.

nulls_first_last_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Force NULLs to end of ascending order (PostgreSQL default)
SELECT name, price FROM products ORDER BY price ASC NULLS LAST;
 
-- Force NULLs to beginning of ascending order (MySQL/SQL Server default)
SELECT name, price FROM products ORDER BY price ASC NULLS FIRST;
 
-- Common pattern: Show products with prices first, NULL prices last
SELECT product_id, name, price
FROM products
ORDER BY price ASC NULLS LAST;
 
-- Force NULLs to end of descending order (opposite of PostgreSQL default)
SELECT name, hire_date FROM employees ORDER BY hire_date DESC NULLS LAST;
 
-- Multi-column with independent NULL handling
SELECT 
    employee_id, 
    department_id, 
    manager_id,
    salary
FROM employees
ORDER BY 
    department_id ASC NULLS FIRST,    -- No department at start
    manager_id ASC NULLS LAST,        -- No manager at end within dept
    salary DESC;

NULLS FIRST/LAST Interaction with Direction

All Combinations of Direction and NULL Position
ORDER BY Clause	Result Order
`ORDER BY col ASC NULLS FIRST`	NULL, 1, 2, 3, ... (NULLs at start)
`ORDER BY col ASC NULLS LAST`	1, 2, 3, ..., NULL (NULLs at end)
`ORDER BY col DESC NULLS FIRST`	NULL, ..., 3, 2, 1 (NULLs at start)
`ORDER BY col DESC NULLS LAST`	..., 3, 2, 1, NULL (NULLs at end)

Common Pattern: NULLS LAST for User-Facing Lists

For most user interfaces, NULLS LAST is preferred. Users typically want to see items with known values first, with 'missing data' items appearing at the bottom of the list. Make NULLS LAST your default choice unless there's a specific reason to show NULLs first.

Workarounds for Databases Without NULLS FIRST/LAST

SQL Server and older MySQL versions don't support NULLS FIRST/LAST syntax. However, you can achieve equivalent behavior using expressions in ORDER BY.

The CASE Expression Workaround

case_workaround.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Emulate NULLS LAST in ascending order (for MySQL/SQL Server)
SELECT name, price 
FROM products 
ORDER BY 
    CASE WHEN price IS NULL THEN 1 ELSE 0 END,  -- NULLs sort last
    price ASC;
 
-- Emulate NULLS FIRST in ascending order (for MySQL/SQL Server)
SELECT name, price 
FROM products 
ORDER BY 
    CASE WHEN price IS NULL THEN 0 ELSE 1 END,  -- NULLs sort first
    price ASC;
 
-- Emulate NULLS LAST in descending order
SELECT name, price 
FROM products 
ORDER BY 
    CASE WHEN price IS NULL THEN 1 ELSE 0 END,  -- NULLs sort last
    price DESC;
 
-- Emulate NULLS FIRST in descending order
SELECT name, price 
FROM products 
ORDER BY 
    CASE WHEN price IS NULL THEN 0 ELSE 1 END,  -- NULLs sort first
    price DESC;

The COALESCE/ISNULL Workaround

For numeric columns, you can use COALESCE with extreme values to push NULLs to desired positions:

coalesce_workaround.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- NULLS LAST in ascending order (replace NULL with huge value)
SELECT name, price 
FROM products 
ORDER BY COALESCE(price, 999999999) ASC;
-- NULLs treated as very large value, sort to end
 
-- NULLS FIRST in ascending order (replace NULL with tiny value)
SELECT name, price 
FROM products 
ORDER BY COALESCE(price, -999999999) ASC;
-- NULLs treated as very small value, sort to start
 
-- NULLS LAST in descending order (replace NULL with tiny value)
SELECT name, price 
FROM products 
ORDER BY COALESCE(price, -999999999) DESC;
 
-- NULLS FIRST in descending order (replace NULL with huge value)
SELECT name, price 
FROM products 
ORDER BY COALESCE(price, 999999999) DESC;
 
-- SQL Server specific: Use ISNULL instead of COALESCE
SELECT name, price 
FROM products 
ORDER BY ISNULL(price, 999999999) ASC;

COALESCE Workaround Limitations

The COALESCE approach has risks: (1) If your data can contain the sentinel value (999999999), ordering breaks; (2) Type mismatches can occur; (3) Performance may suffer if the expression prevents index use. The CASE approach is generally safer.

Comparison of Workarounds

CASE vs COALESCE Workaround Comparison
Aspect	CASE Workaround	COALESCE Workaround
Type safety	✓ Works with any type	⚠ Requires compatible sentinel value
Data collision risk	✓ None	⚠ Sentinel might exist in data
Readability	✓ Clear intent	⚠ Less obvious purpose
Performance	Similar unless indexed	Similar unless indexed
Works for strings	✓ Yes	⚠ Tricky (need lexicographically extreme values)
Works for dates	✓ Yes	✓ Use extreme dates ('1900-01-01', '9999-12-31')

NULL Ordering and Index Optimization

NULL handling in ORDER BY affects index usability. Understanding these dynamics is crucial for performance.

How Indexes Store NULLs

Most B-tree indexes store NULL values in a predictable position:

PostgreSQL: NULLs stored at one end of the index (configurable per index)
Oracle: NULLs are NOT stored in single-column B-tree indexes by default
MySQL InnoDB: NULLs stored at the start of the index
SQL Server: NULLs stored at the start of the index

This physical placement affects whether ORDER BY can use the index without additional sorting.

index_null_ordering.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- PostgreSQL: Create index with explicit NULL ordering to match query
CREATE INDEX idx_price_nulls_last 
ON products (price ASC NULLS LAST);
 
-- Now this query can use the index without sorting:
SELECT * FROM products ORDER BY price ASC NULLS LAST;
 
-- But this query may require a sort:
SELECT * FROM products ORDER BY price ASC NULLS FIRST;
 
-- Create both indexes if both patterns are common:
CREATE INDEX idx_price_nulls_first 
ON products (price ASC NULLS FIRST);
 
-- Oracle: Include NULL in index using function-based index
-- (Standard Oracle doesn't index NULL in single-column indexes)
CREATE INDEX idx_price ON products (price, 0);
-- The constant 0 ensures NULL rows are included in index

Workarounds and Index Impact

workaround_index_impact.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- PROBLEM: Workaround prevents index use
-- This expression prevents the optimizer from using idx_price:
SELECT * FROM products 
ORDER BY 
    CASE WHEN price IS NULL THEN 1 ELSE 0 END,
    price ASC;
 
-- SOLUTION: Expression index (PostgreSQL)
CREATE INDEX idx_price_null_sort 
ON products (
    (CASE WHEN price IS NULL THEN 1 ELSE 0 END),
    price ASC
);
 
-- SOLUTION: Computed column + index (SQL Server)
ALTER TABLE products 
ADD price_null_flag AS (CASE WHEN price IS NULL THEN 1 ELSE 0 END) PERSISTED;
 
CREATE INDEX idx_price_null ON products (price_null_flag, price);
 
-- Now the workaround query can use the index:
SELECT * FROM products 
ORDER BY price_null_flag, price ASC;

Test with EXPLAIN

Always verify NULL ordering performance with EXPLAIN. Check whether the database is using an index scan (good) or performing an explicit sort (potentially bad). The presence of a 'Sort' operation in the plan indicates the index couldn't fully satisfy the ORDER BY.

Real-World NULL Ordering Scenarios

Let's examine practical scenarios where NULL ordering matters and how to handle each appropriately.

Scenario 1: Optional Fields in User Forms

scenario_optional_fields.sql
1
2
3
4
5
6
7
8
9
10
11
12
-- Users with optional "company" field
-- Business rule: Show users with companies first, solo users after
 
SELECT user_id, name, company, created_at
FROM users
ORDER BY company ASC NULLS LAST, created_at DESC;
 
-- Result:
-- (Alice, Acme Corp, ...)
-- (Bob, Beta Inc, ...)
-- (Carol, NULL, ...)     -- Users without company appear last
-- (Dan, NULL, ...)

Scenario 2: Incomplete Data Quality Dashboard

scenario_data_quality.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Data quality review: prioritize records with missing data
-- Business rule: Rows with NULLs need attention, show first
 
SELECT customer_id, email, phone, last_contact_date
FROM customers
ORDER BY 
    -- Prioritize rows with more NULL fields
    (CASE WHEN email IS NULL THEN 1 ELSE 0 END +
     CASE WHEN phone IS NULL THEN 1 ELSE 0 END +
     CASE WHEN last_contact_date IS NULL THEN 1 ELSE 0 END) DESC,
    customer_id ASC;
 
-- Result: Records with most missing fields appear first for cleanup

Scenario 3: Pricing with "Price on Request"

scenario_pricing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- E-commerce: NULL price means "Contact for quote"
-- Business rule: Show priced items sorted by price, then inquire-only items
 
SELECT product_id, name, price,
    CASE WHEN price IS NULL THEN 'Contact for Quote' 
         ELSE CAST(price AS VARCHAR) 
    END AS display_price
FROM products
WHERE category = 'Enterprise'
ORDER BY 
    CASE WHEN price IS NULL THEN 1 ELSE 0 END,  -- NULL prices last
    price ASC;
 
-- Result:
-- (Server X, 4999.00)
-- (Server Y, 7999.00)
-- (Custom Solution, NULL, "Contact for Quote")

Scenario 4: Optional Due Dates

scenario_due_dates.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Tasks with optional due dates
-- Business rule: Show tasks by urgency—dated first (soonest first), undated last
 
SELECT task_id, title, priority, due_date
FROM tasks
WHERE status = 'open'
ORDER BY 
    CASE WHEN due_date IS NULL THEN 1 ELSE 0 END,  -- Dated tasks first
    due_date ASC,                                   -- Soonest due first
    priority ASC;                                   -- Then by priority
 
-- For the reverse (undated = "ongoing" = highest priority):
SELECT task_id, title, priority, due_date
FROM tasks
WHERE status = 'open'
ORDER BY 
    CASE WHEN due_date IS NULL THEN 0 ELSE 1 END,  -- Undated first
    priority ASC,
    due_date ASC;

Business Rules Drive NULL Position

There's no universally 'correct' NULL position. The appropriate choice depends on business context. A missing due date might mean 'not urgent' or 'always active'—the business domain defines which interpretation applies.

Summary: NULL Ordering Mastery

NULL ordering is a subtle but important aspect of SQL that creates cross-platform compatibility challenges. Let's consolidate the essential knowledge:

Key Takeaways

•NULL comparisons yield UNKNOWN — This is why databases must choose a convention for NULL ordering
•Default behaviors differ — PostgreSQL/Oracle treat NULL as largest; MySQL/SQL Server treat NULL as smallest
•NULLS FIRST/LAST provides explicit control — Use it whenever NULL ordering matters to your application
•Workarounds exist for unsupported databases — CASE expressions safely emulate NULLS FIRST/LAST in all systems
•Index optimization requires matching NULL order — PostgreSQL allows index-level NULL ordering specification
•Business context determines correct NULL position — There's no universally right answer; let domain semantics guide you

NULL Ordering Best Practices

•Always specify NULLS FIRST/LAST when it matters
•Use CASE workarounds for portable code
•Document NULL ordering assumptions
•Test with NULL data in sort columns
•Create indexes matching NULL ordering needs

NULL Ordering Pitfalls

•Assuming NULL behavior matches your dev environment
•Using COALESCE with non-extreme sentinel values
•Ignoring NULL ordering in pagination queries
•Forgetting that defaults differ across platforms
•Not testing edge cases with NULL data

portable_null_ordering.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- PORTABLE PATTERN: Always explicit, works everywhere
-- Use CASE workaround even when NULLS LAST is supported
-- for maximum cross-platform compatibility
 
-- "Safe" NULLS LAST for ascending order:
SELECT * FROM products
ORDER BY 
    CASE WHEN price IS NULL THEN 1 ELSE 0 END,
    price ASC;
 
-- "Safe" NULLS FIRST for descending order:
SELECT * FROM products
ORDER BY 
    CASE WHEN price IS NULL THEN 0 ELSE 1 END,
    price DESC;

Page Complete

You now have complete mastery of NULL ordering: its theoretical basis, database-specific defaults, explicit control syntax, portable workarounds, and performance optimization. Next, we'll explore expression ordering—using computed values, functions, and CASE statements to create dynamic sort behaviors.