Find the Second Highest Salary in SQL (Handling Ties & NULLs)

Question

Given an `employee` table with `id` and `salary`, write a SQL query to find the second highest distinct salary. If there is no second highest salary (e.g. only 1 employee or all salaries are equal), the query should return `NULL`.

Accepted Answer

Optimal Solution:
SELECT MAX(salary) AS SecondHighestSalary
FROM employee
WHERE salary < (
  SELECT MAX(salary) 
  FROM employee
);

Technical Explanation:
The subquery `(SELECT MAX(salary) FROM employee)` finds the top salary. The outer query finds the maximum salary strictly less than the top salary. If no smaller salary exists, `MAX()` naturally returns `NULL`.

id	salary
1	100
2	200
3	300