ORDER BY with NULL values behavior in SQL

Introduction

We use ORDER BY to sort data. Sometimes, data has empty spots called NULLs. Knowing how NULLs sort helps us see data clearly.

When you want to list customers by their last purchase date but some dates are missing.

When sorting employee records by salary and some salaries are not recorded yet.

When showing product ratings where some products have no ratings (NULL).

When organizing student grades but some students haven't taken the test (NULL grades).

Syntax

SQL

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

ASC means ascending order (smallest to largest).

DESC means descending order (largest to smallest).

NULLS FIRST puts NULL values at the start.

NULLS LAST puts NULL values at the end.

Examples

Sort players by score from lowest to highest. NULLs usually come last in ascending order in many databases, but some put NULLs first.

SQL

SELECT name, score
FROM players
ORDER BY score ASC;

Sort players by score from highest to lowest, but put NULL scores at the end.

SQL

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

Sort players by score from lowest to highest, but put NULL scores at the end instead of the start.

SQL

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

Sample Program

This creates a table of players with some scores missing (NULL). Then it sorts players by score from lowest to highest, but puts players with no score at the end.

SQL

CREATE TABLE players (name VARCHAR(20), score INT);

INSERT INTO players VALUES
('Alice', 50),
('Bob', NULL),
('Charlie', 70),
('Diana', NULL),
('Eve', 60);

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

Important Notes

Different databases may treat NULLs differently by default.

Using NULLS FIRST or NULLS LAST helps control where NULLs appear in your sorted list.

Without specifying, some databases put NULLs first in ascending order, others put them last.

Summary

ORDER BY sorts data by column values.

NULL values are special and can appear at the start or end when sorting.

Use NULLS FIRST or NULLS LAST to decide where NULLs go in your sorted results.