NULLS FIRST / NULLS LAST

What are NULLS FIRST and NULLS LAST?

By default PostgreSQL places NULLs last when sorting in ascending order and first when sorting in descending order. The NULLS FIRST and NULLS LAST modifiers let you explicitly control this behavior.

Syntax

SELECT * FROM table_name
ORDER BY column ASC NULLS FIRST;

SELECT * FROM table_name
ORDER BY column DESC NULLS LAST;

When to Use

• When you want NULLs to appear at the top or bottom regardless of sort direction
• Reports where missing data should be clearly visible at the end
• Sorting optional fields where NULL means "not yet assigned"

Key Points

1. Default Behavior — ASC puts NULLs last; DESC puts NULLs first.
2. Override — NULLS FIRST forces NULLs to the top; NULLS LAST forces them to the bottom.
3. Per Column — You can set it independently for each ORDER BY column.
4. Index Support — B-tree indexes can be created with NULLS FIRST/LAST for matching sort order.
5. Standard SQL — This syntax is part of the SQL standard.