Second Highest Salary SQL Challenge - MySQL Solution

1. Introduction

In this blog post, we will tackle a common SQL query problem – finding the second highest salary in a table. We'll demonstrate this using the Employee table in a MySQL database.

Problem

The challenge is to write a SQL query to find the second highest salary from the Employee table. If there is no second highest salary (meaning all employees have the same salary or there is only one employee), the query should return null.

Database Table:

Employee Table:

id	salary
1	100
2	200
3	300

3. MySQL Solution

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

Output:

Output Table:

SecondHighestSalary
200

Output for case with no second highest salary:

SecondHighestSalary
null

Explanation:

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

2. It uses a WHERE clause to ensure that the salary is less than the maximum salary in the table.

3. This effectively returns the second highest salary.

4. If there is no second highest salary (due to equal salaries or only one record), the query returns null.