ORDER BY with NULL values behavior in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ORDER BY clause do in SQL?

It sorts the rows returned by a query based on one or more columns, either in ascending (ASC) or descending (DESC) order.

beginner

How are NULL values treated by default when using ORDER BY in SQL?

By default, NULL values are treated as the lowest possible values and appear first when sorting in ascending order, and last when sorting in descending order.

intermediate

What keywords can you use to control the position of NULL values in ORDER BY?

You can use NULLS FIRST or NULLS LAST to explicitly specify whether NULL values appear at the beginning or end of the sorted result.

intermediate

Write a simple SQL query to order a table named 'employees' by the 'salary' column with NULL salaries appearing last.

SELECT * FROM employees ORDER BY salary ASC NULLS LAST;

beginner

Why might controlling NULL ordering be important in real-life data sorting?

Because NULLs represent missing or unknown data, controlling their position helps make reports clearer and ensures important data is prioritized in the sorted list.

By default, where do NULL values appear when you ORDER BY a column in ascending order?

A Randomly

B At the end

C At the beginning

D In the middle

By default, NULL values are treated as the lowest values and appear first in ascending order.

Which clause explicitly places NULL values at the end of the sorted results?

A NULLS LAST

B NULLS FIRST

C ORDER BY NULL

D IS NULL

NULLS LAST forces NULL values to appear at the end of the sorted list.

What happens to NULL values when you ORDER BY a column in descending order without specifying NULLS FIRST or LAST?

A NULLs appear first

B NULLs appear last

C NULLs are ignored

D Query fails

By default, NULLs appear last in descending order.

Which SQL keyword is NOT used to control NULL ordering?

A NULLS MIDDLE

B NULLS LAST

C NULLS FIRST

D ORDER BY

NULLS MIDDLE is not a valid SQL keyword.

If you want NULL values to appear at the top when sorting descending, which clause should you use?

A ORDER BY DESC NULLS LAST

B NULLS LAST

C ORDER BY ASC NULLS FIRST

D NULLS FIRST

NULLS FIRST places NULL values at the beginning, even in descending order.

Explain how NULL values are ordered by default in SQL when using ORDER BY with ascending and descending sorts.

Think about how missing data is treated compared to actual values.

Describe how you can control the position of NULL values in an ORDER BY clause and why this might be useful.

Consider how reports or lists look when missing data is shown first or last.