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.

StackExchange Database Administrators Q#324541, answer score: 2