Control Where NULLs Appear in a Sort

Use `NULLS FIRST` or `NULLS LAST` to explicitly position NULL values at the top or bottom of a sorted result set.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Sort scores ascending with NULLs at the end

Eve (78), Alice (85), and Carol (92) have scores and sort ascending by value. Bob and Dave have NULL scores. In MySQL and SQL Server, (score IS NULL) returns 0 for non-null rows and 1 for null rows — sorting that expression first pushes the NULLs to the bottom. In PostgreSQL and SQLite, NULLS LAST is a direct clause modifier on the ORDER BY column.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  players
VALUES
  ('Alice', 85),
  ('Bob', NULL),
  ('Carol', 92),
  ('Dave', NULL),
  ('Eve', 78);

Validated query

SQL

SELECT
  name,
  score
FROM
  players
ORDER BY
  (score IS NULL) ASC,
  score ASC;

Expected result

name	score
Eve	78
Alice	85
Carol	92
Bob	NULL
Dave	NULL

SQL Server

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  players
VALUES
  ('Alice', 85),
  ('Bob', NULL),
  ('Carol', 92),
  ('Dave', NULL),
  ('Eve', 78);

Validated query

SQL

SELECT
  name,
  score
FROM
  players
ORDER BY
  CASE
    WHEN score IS NULL THEN 1
    ELSE 0
  END ASC,
  score ASC;

Expected result

name	score
Eve	78
Alice	85
Carol	92
Bob	NULL
Dave	NULL

PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  players
VALUES
  ('Alice', 85),
  ('Bob', NULL),
  ('Carol', 92),
  ('Dave', NULL),
  ('Eve', 78);

Validated query

SQL

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

Expected result

name	score
Eve	78
Alice	85
Carol	92
Bob	NULL
Dave	NULL

PostgreSQL and SQLite use native NULLS LAST syntax. MySQL/MariaDB prefix with (score IS NULL) ASC — which evaluates to 0 for non-null and 1 for null, pushing nulls last. SQL Server uses an equivalent CASE expression.

Useful when

Where this command helps.

pushing rows with missing data to the end of a report regardless of sort direction
ensuring NULLs sort consistently across databases in cross-engine queries

Explanation

What the command is doing.

By default, NULL ordering varies by engine: PostgreSQL and SQLite treat NULLs as greater than any non-null value, so they sort last with ASC and first with DESC. MySQL, MariaDB, and SQL Server treat NULLs as less than any non-null value, so they sort first with ASC. The ANSI-standard NULLS FIRST and NULLS LAST modifiers let you override the default explicitly. PostgreSQL and SQLite support this syntax natively. MySQL, MariaDB, and SQL Server do not — use a CASE expression that maps NULL to 0 or 1 as a sort prefix instead.

Control Where NULLs Appear in a Sort

Sort scores ascending with NULLs at the end

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.