SQL NULLS

What does SQL NULLS FIRST / NULLS LAST do?

Controls whether NULL values appear first or last when sorting query results.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NULLS: Supported: PostgreSQL, Oracle, BigQuery, Snowflake, Redshift, DB2, SQLite ≥3.30. Not supported: MySQL, MariaDB, SQL Server.

SQL NULLS Full Explanation

The NULLS modifier is used in an ORDER BY clause to explicitly position NULL values either at the beginning (NULLS FIRST) or the end (NULLS LAST) of the sorted result set. Without this clause, SQL engines apply their own defaults: in ascending order, most databases treat NULL as the lowest possible value, while in descending order they treat NULL as the highest. NULLS FIRST and NULLS LAST give developers deterministic, cross-dialect control over null placement, which is essential for accurate ranking, reporting, and pagination. The feature became part of the SQL:2003 standard, but support varies: PostgreSQL, Oracle, BigQuery, Redshift, Snowflake, and modern SQLite versions honor it, whereas MySQL and SQL Server do not. When unsupported, similar behavior can be emulated with expressions such as ORDER BY column IS NULL, column.

SQL NULLS Syntax

SELECT column_list
FROM table_name
ORDER BY sort_column [ASC | DESC] NULLS FIRST;

SELECT column_list
FROM table_name
ORDER BY sort_column [ASC | DESC] NULLS LAST;

SQL NULLS Parameters

- sort_column (column or expression) - The value used to order rows.
- ASC | DESC (keyword) - Optional direction of the sort. Default is ASC.
- NULLS FIRST | NULLS LAST (keyword) - Chooses where NULLs appear in the order.

Example Queries Using SQL NULLS

-- Place inactive users (NULL last_login) at the bottom
SELECT id, last_login
FROM users
ORDER BY last_login NULLS LAST;

-- Show tasks with no due date at the top, then latest first
SELECT task_id, due_date
FROM tasks
ORDER BY due_date DESC NULLS FIRST;

Expected Output Using SQL NULLS

Rows are sorted by the specified column, and any NULL values are positioned either before all non-NULL values (NULLS FIRST) or after them (NULLS LAST)

Use Cases with SQL NULLS

Ensure NULLs appear at the bottom of paginated tables in web apps.
Rank products by sales while showing unknown sales first for investigation.
Produce reports where missing dates must appear at the top for data-quality review.
Achieve deterministic ordering in ETL jobs that rely on sorted input.

Common Mistakes with SQL NULLS

Assuming NULLS FIRST/LAST is supported in MySQL or SQL Server.
Forgetting that the default placement of NULLs changes with ASC vs DESC.
Writing NULLS FIRST after DESC in engines that require the keyword order to be DESC NULLS FIRST.
Believing NULLS FIRST/LAST affects GROUP BY or DISTINCT; it only applies to ORDER BY results.

First Introduced In

SQL:2003

Frequently Asked Questions

What is the default position of NULLs when I sort without specifying NULLS FIRST/LAST?

In ascending order, most engines place NULLs first. In descending order, they place NULLs last. Always check your database documentation because defaults vary.

How can I mimic NULLS LAST in MySQL or SQL Server?

Use a boolean expression in ORDER BY: ORDER BY column IS NULL, column; This orders by whether the value is NULL (false before true) and then by the actual column.

Can I combine NULLS FIRST/LAST with multiple sort keys?

Yes. Specify NULLS FIRST/LAST for each key independently: ORDER BY priority DESC NULLS LAST, created_at NULLS FIRST;

Does NULLS FIRST/LAST change how aggregates treat NULLs?

No. It only affects row ordering. Aggregate functions like COUNT or SUM still ignore NULLs unless stated otherwise.