Objective

Write a SQL query to find the second highest salary from the employee table.

Table Schema:

• employee

Task Requirements:

• Find the second highest salary across all employees
• If there is no second highest salary (e.g., all salaries are the same or only one employee exists), return NULL
• Display the result as second_highest_salary
  
  

Output columns: second_highest_salary

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

Explanation

Step 1: Understanding the Problem

We need the second highest distinct salary value. If multiple employees share the highest salary, we still want the next distinct value below that.

Step 2: Finding the Maximum Salary (Subquery)

SELECT MAX(salary) FROM employee

Identifies the highest salary in the table, used as the reference point.

Step 3: Filtering Salaries Below the Maximum

WHERE salary < (SELECT MAX(salary) FROM employee)

Filters out all salaries equal to or greater than the maximum, leaving only salaries strictly below the highest.

Step 4: Finding the Second Highest

SELECT MAX(salary) AS second_highest_salary

Taking MAX() of the filtered set returns the highest value below the overall maximum - the second highest salary.

Step 5: Handling Edge Cases

If all employees share the same salary or only one employee exists, the WHERE clause filters all rows and MAX() returns NULL automatically.