SQL Interview Prep · Lesson

Returning NULL When No Nth Value Exists

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

Returning NULL When No Nth Value Exists is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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

Fix 1: wrap in an outer SELECT

The simplest reliable fix: make the entire Nth-highest query a scalar subquery inside a single SELECT. A scalar subquery that matches no rows evaluates to NULL, and the outer SELECT always produces exactly one row.

This is the canonical answer to the LeetCode-style "return NULL" variant and works in every dialect.

SELECT (
  SELECT DISTINCT salary
  FROM employee
  ORDER BY salary DESC
  LIMIT 1 OFFSET 2   -- N = 3
) AS third_highest;

Why the scalar subquery trick works

Two rules combine to give the behavior you want:

A scalar subquery must return at most one value. If it returns no rows, SQL substitutes NULL.
The outer SELECT with no FROM (or a single-row source) always emits exactly one row.

So when the inner query finds the Nth value you get it; when it finds nothing you get one row holding NULL. Exactly the contract the interviewer stated.

Fix 1 with the DENSE_RANK version

The same wrapper works around the window-function solution. Put the ranked query inside the scalar subquery; if no row has rank N, the subquery yields NULL and the outer SELECT still returns one row.

SELECT (
  SELECT salary
  FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employee
  ) t
  WHERE rnk = 3
) AS third_highest;

Fix 2: MAX returns NULL for free

Recall the MAX-below-MAX idea from lesson 1. An aggregate over zero rows returns NULL and still produces one row. For the second highest this is a clean one-liner that already satisfies the NULL requirement.

The catch: extending pure MAX nesting to arbitrary N gets ugly, so this is best for the 2nd-highest case specifically.

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Fix 3: COALESCE with a fallback

If your environment guarantees one row but the value might be missing in some other way, you can wrap the result in COALESCE to supply an explicit default.

Note: COALESCE only helps once a row exists. It does not turn an empty result set into a row. So combine it with the scalar-subquery wrapper (which guarantees a row), then COALESCE the value if you want something other than NULL, like 0.

SELECT COALESCE((
  SELECT DISTINCT salary
  FROM employee
  ORDER BY salary DESC
  LIMIT 1 OFFSET 2
), 0) AS third_highest_or_zero;

What does NOT fix it

Beware of fixes that look right but fail:

Adding COALESCE directly around a query that returns zero rows does nothing; there is no row for COALESCE to act on.
IFNULL / ISNULL have the same limitation as COALESCE.
Adding LIMIT 1 does not invent a row when none qualified.

The row-count problem must be solved with the scalar-subquery wrapper or an aggregate, not with NULL-substitution functions alone.

Worked example: asking for the 3rd of two

Salaries: 500, 500, 300. Distinct salaries are just 500 and 300, so there is no 3rd highest.

Plain DENSE_RANK with WHERE rnk = 3: returns zero rows. Fails the spec.
Scalar-subquery wrapper: inner query finds nothing, so the outer SELECT returns one row: NULL. Passes.
COALESCE(..., 0): returns one row: 0, if a numeric default was requested.

Talking through it in the interview

Score points by narrating:

"The naive query returns an empty set, not NULL, so I'll wrap it in a scalar subquery to guarantee one row."
"A scalar subquery with no matching rows evaluates to NULL, which is exactly the contract."
"If you'd prefer a default like 0 instead of NULL, I'll add COALESCE around the subquery."

Demonstrating you understand row-count vs value semantics is the whole point of this question.

Putting it all together

A robust, parameterizable Nth-highest-or-NULL solution: rank distinct salaries, filter for rank N inside a scalar subquery, and let the outer SELECT guarantee a single row.

This one query handles duplicates (via DENSE_RANK), generalizes to any N, and returns NULL gracefully when N exceeds the number of distinct salaries.

SELECT (
  SELECT salary
  FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employee
  ) t
  WHERE rnk = :n
  LIMIT 1
) AS nth_highest;

Quick Check

Reason about row counts versus NULL values.

Recap

When N exceeds the available distinct salaries, a plain ranking query returns an empty set, not NULL.

Wrap the Nth-highest query in a scalar subquery inside an outer SELECT so it always produces one row, yielding NULL when no value matches.
The MAX-below-MAX form returns NULL for free for the second-highest case.
COALESCE only substitutes a value once a row exists; it cannot turn zero rows into one.

Always distinguish row-count from value when an interviewer asks for graceful NULL handling.

Frequently asked questions

Is the “Returning NULL When No Nth Value Exists” lesson free?

Yes — the full text of “Returning NULL When No Nth Value Exists” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Returning NULL When No Nth Value Exists”?

The edge case interviewers love: gracefully handling too-few rows. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Returning NULL When No Nth Value Exists” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep