ORDER BY with NULL values behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

How does ORDER BY sort NULLs by default?

Consider a table Employees with a column salary that contains some NULL values. What is the order of salaries when you run this query?

SELECT salary FROM Employees ORDER BY salary ASC;

Assume the salaries are: 50000, NULL, 70000, 60000.

SQL

CREATE TABLE Employees (salary INT);
INSERT INTO Employees VALUES (50000), (NULL), (70000), (60000);
SELECT salary FROM Employees ORDER BY salary ASC;

A [50000, 60000, 70000, NULL]

B [NULL, 50000, 60000, 70000]

C [70000, 60000, 50000, NULL]

D [60000, 70000, 50000, NULL]

💡 Hint

Think about where NULL values appear when sorting ascending by default in most SQL databases.

By default, in standard SQL and many databases like PostgreSQL, NULL values are sorted last when ordering ascending. So the non-NULL salaries come first in ascending order, then NULLs.

❓ query_result

intermediate

How does ORDER BY sort NULLs with DESC?

Using the same Employees table, what is the order of salaries when you run:

SELECT salary FROM Employees ORDER BY salary DESC;

Given salaries: 50000, NULL, 70000, 60000.

SQL

CREATE TABLE Employees (salary INT);
INSERT INTO Employees VALUES (50000), (NULL), (70000), (60000);
SELECT salary FROM Employees ORDER BY salary DESC;

A [NULL, 70000, 60000, 50000]

B [70000, 60000, 50000, NULL]

C [50000, 60000, 70000, NULL]

D [60000, 70000, 50000, NULL]

💡 Hint

Consider where NULLs appear when sorting descending by default.

By default, in standard SQL and many databases like PostgreSQL, NULL values are sorted last when ordering descending. So the non-NULL salaries come first in descending order, then NULLs.

📝 Syntax

advanced

Which query correctly places NULLs first when ordering ascending?

You want to sort the salary column ascending but have NULL values appear first. Which query does this correctly?

A SELECT salary FROM Employees ORDER BY NULLS FIRST salary ASC;

B SELECT salary FROM Employees ORDER BY salary NULLS FIRST ASC;

C SELECT salary FROM Employees ORDER BY salary ASC NULLS FIRST;

D SELECT salary FROM Employees ORDER BY salary ASC FIRST NULLS;

💡 Hint

The NULLS FIRST clause comes after ASC or DESC in standard SQL.

The correct syntax is to put NULLS FIRST or NULLS LAST after the ASC or DESC keyword. Option C follows this syntax.

🧠 Conceptual

advanced

Why might NULLS FIRST or NULLS LAST be important in sorting?

Which of these best explains why you might want to use NULLS FIRST or NULLS LAST in an ORDER BY clause?

A To change the data type of NULL values to a default value.

B To convert NULL values into zeros before sorting.

C To automatically remove rows with NULL values from the result set.

D To control whether missing or unknown values appear at the start or end of sorted results for clearer data interpretation.

💡 Hint

Think about how NULLs represent missing data and how their position affects reading sorted data.

NULLS FIRST or NULLS LAST lets you decide where missing values appear in sorted results, helping make reports or queries easier to understand.

🔧 Debug

expert

Identify the error in this ORDER BY with NULLS clause

This query is intended to sort salaries ascending with NULLs last, but it causes an error:

SELECT salary FROM Employees ORDER BY salary NULLS LAST ASC;

What is the cause of the error?

SQL

SELECT salary FROM Employees ORDER BY salary NULLS LAST ASC;

A The NULLS LAST clause must come after ASC or DESC, not before.

B NULLS LAST is not supported in ORDER BY clauses.

C The salary column must be cast to a non-nullable type first.

D The query is missing a WHERE clause to filter NULLs.

💡 Hint

Check the order of keywords in the ORDER BY clause.

The correct syntax requires ASC or DESC first, then NULLS FIRST or NULLS LAST. Placing NULLS LAST before ASC causes a syntax error.