Second Highest Salary | LeetCode 176

1. Problem Statement & Overview

The objective of LeetCode Problem 176 is to write a SQL query to find the second highest distinct salary from the Employee table. If there is no second highest salary (e.g., if there are fewer than two unique salaries in the table), the query must return NULL.

Table Schema

• Table Name: Employee
• Columns:
  • id (INT): The primary key column containing unique values for each row.
  • salary (INT): Contains information about the salary of an employee.

Example Scenarios

Example 1: Multiple Distinct Salaries

• Expected Output: 200 (since 300 is the highest, and 200 is the second highest).

Example 2: Single Row / Insufficient Data

• Expected Output: NULL (since there is no second salary to compare against).

2. Core Mental Model & Approach Strategy

To solve this interview classic, multiple SQL strategies can be employed:

• Self-Join Strategy: Comparing records within the same table to identify which salaries are strictly greater than others.
• Subquery / Aggregation Strategy: Using MAX() combined with a NOT IN or != condition.
• Common Table Expressions (CTEs) & Window Functions: Utilizing DENSE_RANK() or ROW_NUMBER() ordered by salary in descending order.

This guide focuses primarily on the Self-Join Approach, which is intuitive, handles comparisons gracefully, and neatly satisfies edge cases.

3. Naive vs. Optimal Thinking Process

• Naive Thought Process: A beginner might think of sorting the table in descending order and picking the second row (e.g., using LIMIT 1 OFFSET 1). While this works for MySQL, it breaks or requires complex handling when the table has only one row (failing to return NULL as required by LeetCode, instead returning an empty result set).
• Robust Approach: Using a Self-Join or Window Function ensures that if a second highest salary does not exist, the aggregate function naturally returns NULL rather than an empty set.

4. Step-by-Step Self-Join Execution

Step 1: Setting up the Self-Join

We treat the single Employee table as two distinct logical tables, e1 and e2, and perform an Inner Join between them.

SELECT *
FROM Employee e1
JOIN Employee e2
  ON e1.salary < e2.salary;

Step 2: Visualizing the Self-Join Intermediate State

Consider a table with salaries [100, 200, 300].

• Table e1 represents the candidate salaries.
• Table e2 represents salaries that are strictly greater than e1.

Notice what happened here:

• For e1.salary = 100, there are two higher salaries (200 and 300).
• For e1.salary = 200, there is one higher salary (300).
• For e1.salary = 300, there are no higher salaries, so it does not appear in an INNER JOIN where e1.salary < e2.salary.

Step 3: Extracting the Target Salary

Among all the salaries in e1 that have at least one higher salary in e2, we want the maximum of those values. That maximum value will point directly to the second highest salary in the overall table.

Max(e1.salary) among filtered rows = 200

5. Final SQL Query (Self-Join Approach)

Here is the clean, concise 3-line SQL query using the self-join method:

SELECT MAX(e1.salary) AS SecondHighestSalary
FROM Employee e1
JOIN Employee e2
  ON e1.salary < e2.salary;

6. Alternative Approaches (Quick Comparison)

7. Real-World Pitfalls & Edge Cases

• Handling Duplicate Salaries: If two employees share the highest salary (e.g., [300, 300, 200, 100]), a strict < join condition handles duplicates cleanly by evaluating distinct salary tiers.
• Empty Tables / Single Row: If the table has 0 or 1 row, MAX() over the result set evaluates to NULL rather than returning an empty dataset, satisfying the LeetCode constraint.
• Column Aliasing: Always ensure the resulting column is aliased properly as requested by the problem specification (AS SecondHighestSalary).

Summary and Key Takeaways

• Problem Objective: Find the exact second highest distinct salary from a database table and return NULL if it doesn't exist.
• Self-Join Concept: Joining a table to itself using an inequality condition (e1.salary < e2.salary) filters and pairs lower salaries with all values strictly greater than them.
• Aggregation Power: Wrapping e1.salary in MAX() extracts the highest of those lower tiers, which corresponds mathematically to the second highest overall salary.
• NULL Safety: Aggregate functions like MAX() naturally return NULL when no rows match the criteria, avoiding empty set errors on single-row tables.
• Interview Readiness: This is a top-tier foundational SQL question frequently asked to test understanding of table self-joins and aggregate functions.