SQL Interview Prep · Lesson

Returning NULL When No Nth Value Exists

The edge case interviewers love: gracefully handling too-few rows.

The Edge Case Interviewers Love

After you nail the Nth-highest query, the interviewer adds: "What if the table has fewer than N distinct salaries? I want a single NULL, not an empty result."

This is the question that separates candidates who memorized a query from those who understand result-set behavior. Many solutions silently return zero rows instead of one row containing NULL.

This lesson is all about forcing exactly one output row, whose value is NULL when no Nth value exists.

Why DENSE_RANK alone returns no rows

Recall the standard Nth-highest query. If there are only two distinct salaries and you ask for the 3rd, WHERE rnk = 3 matches nothing, so the query returns an empty set: zero rows.

An empty set is not the same as a row containing NULL. If the spec says "return NULL," an empty result fails the test, even though the underlying logic is correct.

SELECT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) t
WHERE rnk = 3;  -- returns NO rows if fewer than 3 distinct salaries

All lessons in this course

← Back to SQL Interview Prep