NULL Ordering

Definition

NULL represents "unknown" or "missing," so where should it sort — before the smallest value, or after the largest? SQL standard and most engines let you control this explicitly with NULLS FIRST / NULLS LAST:

sql
SELECT column1 FROM table_name ORDER BY column1 NULLS FIRST;
SELECT column1 FROM table_name ORDER BY column1 NULLS LAST;

But if you don't specify one, the default behavior differs by database engine — a genuine, well-documented dialect trap.

The Dialect Difference

• PostgreSQL: default is NULLS LAST for ASC, and NULLS FIRST for DESC. (NULLs are treated as "larger than any value.")
• MySQL: NULLs sort as the smallest possible value, so in ASC order NULLs come first by default, and in DESC order they come last. MySQL does not support NULLS FIRST/LAST syntax directly (as of common versions) — you emulate it with ORDER BY column IS NULL, column.
• SQL Server: like MySQL, NULLs sort first in ASC order by default, and also lacks native NULLS FIRST/LAST syntax.

This means the same query, same data, same ASC keyword can put NULL rows at opposite ends of the result depending on which engine runs it.

Worked Example (PostgreSQL)

sql
INSERT INTO products (id, name, category, price, rating) VALUES
 (8, 'Mystery Box', 'Misc', NULL, 3.0);

SELECT name, price FROM products ORDER BY price ASC;

PostgreSQL result (NULL sorts LAST by default in ASC):

 name               | price
--------------------+--------
 Notebook Set       | 199.00
 Yoga Mat           | 499.00
 Wireless Mouse     | 599.00
 Desk Lamp          | 899.00
 Bluetooth Speaker  | 1499.00
 Mystery Box        | NULL

To force NULLs to the top instead:

sql
SELECT name, price FROM products ORDER BY price ASC NULLS FIRST;

 name               | price
--------------------+--------
 Mystery Box        | NULL
 Notebook Set       | 199.00
 ...

On MySQL, the plain ORDER BY price ASC would instead put Mystery Box (NULL) first by default — the opposite of PostgreSQL's default — because MySQL treats NULL as the lowest value.

Key Takeaways

• Q: Does ASC always put NULLs last?

A: No — that's only PostgreSQL's default. MySQL and SQL Server put NULLs first in ASC by default.

• Q: How do you make NULL ordering deterministic across engines?

A: Always specify NULLS FIRST/NULLS LAST explicitly (Postgres/standard SQL) or an equivalent CASE/IS NULL trick (MySQL/SQL Server), rather than relying on engine defaults.

• Q: Why does this matter in practice?

A: Code that "works" in local testing on one engine can silently misorder NULLs when ported to another engine or a different DB version.