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?