# Selecting second highest value using two SQL queries returns different results

I have the following tables from which I am to select the second highest salary:

Table 1:

id salary
1 100
2 200
3 300

## Table 2:

id salary
1 100

I wrote two SQL queries to achieve this:

Query 1:

``````SELECT salary AS SecondHighestSalary
FROM Employee
WHERE salary NOT IN (SELECT MAX(salary) FROM Employee)
ORDER BY salary DESC
LIMIT 1;
``````

Query 2:

``````SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary NOT IN (SELECT MAX(salary) FROM Employee);
``````

Both queries work for table 1. However, in the case where there is no second highest salary, like with table 2, only the second query provides null, while the first query just provides an empty table. Why do they both not provide null?

These queries are fundamentally different. One has a scalar aggregation, and one does not.

This query is selecting the first row which is not the maximum. If there is no row then no results will be returned.

``````SELECT salary AS SecondHighestSalary
FROM Employee
WHERE salary NOT IN (SELECT MAX(salary) FROM Employee)
ORDER BY salary DESC
LIMIT 1;
``````

This query is doing scalar aggregation (no `GROUP BY`), which is defined to always return exactly one row. If there are no results then a `NULL` is returned, except for `COUNT` which returns `0`.

``````SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary NOT IN (SELECT MAX(salary) FROM Employee);
``````

As a side note: both these queries require two lookups on the table. A more efficient method is to use an offset.

``````SELECT salary AS SecondHighestSalary
FROM Employee
ORDER BY salary DESC
LIMIT 1, 1;
``````

To ensure you get a `NULL` instead of no rows, you can always wrap it in a subquery

``````SELECT (
SELECT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1, 1
) AS SecondHighestSalary;
``````

This method is supported in many other DBMSs using `OFFSET FETCH` syntax.