ORDER BY with NULLS FIRST and NULLS LAST in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does ORDER BY column NULLS FIRST do in PostgreSQL?

It sorts the results by the specified column, placing all NULL values at the beginning of the sorted list before non-NULL values.

beginner

How does ORDER BY column NULLS LAST affect the sorting order?

It sorts the results by the specified column, placing all NULL values at the end of the sorted list after non-NULL values.

beginner

By default, where do NULL values appear when sorting in ascending order without specifying NULLS FIRST or NULLS LAST?

In ascending order, NULL values appear last by default.

beginner

By default, where do NULL values appear when sorting in descending order without specifying NULLS FIRST or NULLS LAST?

In descending order, NULL values appear first by default.

beginner

Write a PostgreSQL query to select all rows from a table employees ordered by salary ascending, but with NULL salaries shown first.

SELECT * FROM employees ORDER BY salary ASC NULLS FIRST;

In PostgreSQL, what does ORDER BY age DESC NULLS LAST do?

A Sorts by age descending, placing NULLs at the end

B Sorts by age ascending, placing NULLs at the beginning

C Sorts by age descending, placing NULLs at the beginning

D Sorts by age ascending, placing NULLs at the end

The query sorts ages from highest to lowest and puts NULL values after all non-NULL ages.

What is the default position of NULLs when sorting ascending without specifying NULLS FIRST or LAST?

A At the beginning

B At the end

C Randomly placed

D Depends on the data type

By default, NULLs appear last in ascending order.

Which clause explicitly places NULL values at the start when sorting?

A NULLS LAST

B NULLS TOP

C NULLS FIRST

D NULLS BEGIN

NULLS FIRST places NULL values before non-NULL values in the sort order.

If you want NULLs to appear last in descending order, which clause should you use?

A NULLS LAST

B No clause needed

C NULLS FIRST

D ORDER BY NULLS LAST DESC

NULLS LAST explicitly places NULLs after non-NULL values even in descending order.

What happens if you omit NULLS FIRST or NULLS LAST in an ORDER BY clause?

A NULLs are ignored

B NULLs are always first

C Query fails with error

D Default NULL placement depends on ASC or DESC

PostgreSQL uses default NULL placement: last for ASC, first for DESC.

Explain how PostgreSQL handles NULL values in ORDER BY clauses and how NULLS FIRST and NULLS LAST change this behavior.

Think about where NULLs appear by default and how the clauses move them.

Write a query to order a table by a column with NULL values shown first, and explain why you might want to do this.

Use ORDER BY column ASC NULLS FIRST.