Second Highest Salary SQL Challenge - PostgreSQL Solution

1. Introduction

This post explores a common SQL challenge: finding the second-highest salary in a database. We'll address this problem using the Employee table in a PostgreSQL database environment.

Problem

The task is to write a SQL query to find the second highest salary from the Employee table. If there is no second-highest salary (for example, if all employees have the same salary or there's only one employee), the query should return null.

Database Table:

Employee Table:

id	salary
1	100
2	200
3	300

3. PostgreSQL Solution

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

Output:

Output Table:

SecondHighestSalary
200

Output for case with no second highest salary:

SecondHighestSalary
null

Explanation:

1. The query selects the maximum salary from the Employee table.

2. It uses a WHERE clause with NOT IN to exclude the highest salary.

3. This approach effectively fetches the second-highest salary.

4. If there is no second-highest salary, the query returns null.