ORDER BY with NULLS FIRST and NULLS LAST in PostgreSQL

Introduction

We use ORDER BY to sort data. Sometimes, data has empty values called NULLs. NULLS FIRST and NULLS LAST help decide if these empty values come at the start or end of the list.

When you want to list customers by their last purchase date but show those who never purchased (NULL) at the top.

When sorting product prices but want products without a price (NULL) to appear last.

When showing employee records sorted by their manager ID, placing those without a manager (NULL) first.

When displaying event dates and you want events without a date (NULL) to appear at the end.

Syntax

PostgreSQL

SELECT column1, column2
FROM table_name
ORDER BY column_name [ASC|DESC] NULLS {FIRST|LAST};

ASC means ascending order (smallest to largest), DESC means descending (largest to smallest).

NULLS FIRST puts NULL values at the beginning, NULLS LAST puts them at the end.

Examples

Sort players by score from highest to lowest. Players without a score (NULL) appear at the end.

PostgreSQL

SELECT name, score
FROM players
ORDER BY score DESC NULLS LAST;

Sort products by price from lowest to highest. Products without a price (NULL) appear at the start.

PostgreSQL

SELECT product, price
FROM products
ORDER BY price ASC NULLS FIRST;

Sort employees by their manager ID, showing those without a manager (NULL) first.

PostgreSQL

SELECT employee, manager_id
FROM employees
ORDER BY manager_id ASC NULLS FIRST;

Sample Program

This creates a temporary table with some items and their ratings. Some ratings are NULL (unknown). The query sorts items by rating from highest to lowest, placing items with no rating at the end.

PostgreSQL

CREATE TEMP TABLE items (id SERIAL, name TEXT, rating INT);

INSERT INTO items (name, rating) VALUES
('Item A', 5),
('Item B', NULL),
('Item C', 3),
('Item D', NULL),
('Item E', 4);

SELECT name, rating
FROM items
ORDER BY rating DESC NULLS LAST;

Important Notes

By default, in PostgreSQL, ORDER BY ASC puts NULLs first, and ORDER BY DESC puts NULLs last.

Using NULLS FIRST or NULLS LAST lets you control this behavior explicitly.

This helps make your sorted lists clearer and easier to understand.

Summary

ORDER BY sorts data by columns.

NULLS FIRST and NULLS LAST decide where empty (NULL) values appear in the sorted list.

Use them to make your data lists more meaningful and easier to read.