Skip to main content

What do NULLS FIRST and NULLS LAST do?

SQLJunior~1 min read
Suggest an edit

NULLS FIRST and NULLS LAST are parameters that control where NULL values end up when sorting in SQL.

By default NULL is treated as an undefined value, and different DBMSes (PostgreSQL, Oracle, SQLite, and others) place it differently. To set the order explicitly, use these parameters.

NULLS FIRST

Places all NULLs at the start of the result.

sql
SELECT name, salary FROM employees ORDER BY salary ASC NULLS FIRST;

Even with ascending order, rows with NULL come first.

NULLS LAST

Places all NULLs at the end of the result.

sql
SELECT name, salary FROM employees ORDER BY salary DESC NULLS LAST;

Regardless of the descending order, empty values end up at the very bottom of the list.

Why this matters

Without NULLS FIRST/LAST, the order of rows with NULL can differ:

  • in some systems they're treated as "less than everything",
  • in others, as "greater than everything".

So these parameters exist to let you control the position of empty values explicitly.

Summary:

  • NULLS FIRST: NULL at the start of the result;
  • NULLS LAST: NULL at the end;
  • used with ORDER BY to set an exact sort order when there are gaps in the data.

Short Answer

Interview ready
Premium

A concise answer to help you respond confidently on this topic during an interview.

Suggest an edit