LeetCode 176: Second Highest Salary Solution in SQL Explained

Finding the second highest salary in a table might feel like identifying the runner-up in a payroll race, and LeetCode 176: Second Highest Salary is an easy-level challenge that makes it approachable! Given an Employee table with columns id and salary, you need to write an SQL query to report the second highest salary, returning null if it doesn’t exist. In this blog, we’ll solve it with SQL, exploring two solutions—Subquery with LIMIT and OFFSET (our best solution) and Subquery with NOT IN (a practical alternative). With step-by-step examples, detailed query breakdowns, and tips, you’ll master this problem. While this is an SQL challenge, you can explore Python basics at Python Basics for related coding skills. Let’s find that runner-up salary!

Problem Statement

In LeetCode 176, you’re given an Employee table:

  • id (int, primary key)
  • salary (int)

Your task is to write an SQL query to return the second highest salary from the table. If there is no second highest salary (e.g., fewer than 2 distinct salaries), return null. This differs from table joining like LeetCode 175: Combine Two Tables, focusing on aggregation and ranking rather than merging data.

Constraints

  • Table has at least 1 row.
  • salary values are non-negative integers.
  • Duplicate salaries may exist.

Example

Let’s see some cases:

Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output:
+-----------+
| SecondHighestSalary |
+-----------+
| 200       |
+-----------+
Explanation: 300 is highest, 200 is second highest.

Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
Output:
+-----------+
| SecondHighestSalary |
+-----------+
| null      |
+-----------+
Explanation: Only one salary, no second highest.

Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 100    |
| 3  | 200    |
+----+--------+
Output:
+-----------+
| SecondHighestSalary |
+-----------+
| 100       |
+-----------+
Explanation: 200 is highest, 100 is second (duplicates count).

These examples show we’re finding the second highest distinct salary.

Understanding the Problem

How do you solve LeetCode 176: Second Highest Salary in SQL? We need to:

  • Identify the highest salary.
  • Find the next highest salary below it.
  • Return null if no second highest exists (e.g., only one distinct salary).

For [100,200,300], the highest is 300, second is 200. For [100], there’s no second, so null. For [100,100,200], 200 is highest, 100 is second. We need to handle duplicates (distinct salaries) and edge cases (single row), ideally in a concise query. This isn’t a Python coding task like LeetCode 174: Dungeon Game; it’s about SQL ranking. We’ll use: 1. Subquery with LIMIT and OFFSET: Efficient, clear—our best solution. 2. Subquery with NOT IN: Alternative approach.

Let’s dive into the best solution.

Best Solution: Subquery with LIMIT and OFFSET Approach

Explanation

Subquery with LIMIT and OFFSET finds the second highest salary by:

  • Selecting distinct salaries, ordering descending.
  • Using LIMIT 1 OFFSET 1 to skip the highest and take the next.
  • Wrapping in a subquery with IFNULL to return null if no result.

This ensures a single-pass approach with database optimization (O(n log n) or better with indexing), returning null when needed, making it efficient and straightforward.

For [100,200,300]:

  • Subquery: SELECT DISTINCT salary ORDER BY salary DESC LIMIT 1 OFFSET 1 → 200.
  • Wrap: IFNULL(200, NULL) → 200.

Step-by-Step Example

Example 1: Employee = [100,200,300]

Goal: Return 200.

  • Step 1: Subquery for second highest.
    • SELECT DISTINCT salary FROM Employee → [100,200,300].
    • ORDER BY salary DESC → [300,200,100].
    • LIMIT 1 OFFSET 1 → Skip 300, take 200.
  • Step 2: Wrap with IFNULL.
    • IFNULL(200, NULL) → 200 (not null).
  • Finish: Return 200.

Example 2: Employee = [100]

Goal: Return null.

  • Step 1: Subquery.
    • DISTINCT salary → [100].
    • ORDER BY DESC → [100].
    • LIMIT 1 OFFSET 1 → No result (empty).
  • Step 2: Wrap.
    • IFNULL(NULL, NULL) → null.
  • Finish: Return null.

Example 3: Employee = [100,100,200]

Goal: Return 100.

  • Step 1: Subquery.
    • DISTINCT salary → [100,200].
    • ORDER BY DESC → [200,100].
    • LIMIT 1 OFFSET 1 → 100.
  • Step 2: Wrap.
    • IFNULL(100, NULL) → 100.
  • Finish: Return 100.

How the Code Works (Subquery with LIMIT and OFFSET) – Detailed Line-by-Line Explanation

Here’s the SQL query with a thorough breakdown:

-- Line 1: Select with IFNULL wrapper
SELECT 
    IFNULL(
        -- Line 2: Subquery for second highest
        (SELECT DISTINCT salary 
         FROM Employee 
         ORDER BY salary DESC 
         LIMIT 1 OFFSET 1),
        -- Subquery gets second highest (e.g., 200 from [300,200,100])

        -- Line 3: Null if no result
        NULL
        -- Default if subquery empty (e.g., for [100])
    ) AS SecondHighestSalary
-- Alias for output column (e.g., 200 or null)

This detailed breakdown clarifies how the subquery with LIMIT and OFFSET efficiently finds the second highest salary.

Alternative: Subquery with NOT IN Approach

Explanation

Subquery with NOT IN finds the second highest by:

  • Selecting the max salary where salary < (max salary overall).
  • Using a subquery to exclude the highest salary.
  • Wrapping with IFNULL for null cases.

It’s a practical alternative, also O(n) time with proper indexing, but may be less efficient due to the NOT IN operation and multiple table scans, still O(1) space beyond result.

For [100,200,300]:

  • Max salary = 300.
  • Max where < 300 = 200.
  • Wrap: IFNULL(200, NULL) → 200.

Step-by-Step Example (Alternative)

For [100,200,300]:

  • Step 1: Subquery for max.
    • SELECT MAX(salary) → 300.
  • Step 2: Subquery for second.
    • WHERE salary NOT IN (300) → [100,200].
    • MAX(salary) → 200.
  • Step 3: Wrap.
    • IFNULL(200, NULL) → 200.
  • Finish: Return 200.

How the Code Works (Subquery with NOT IN)

SELECT 
    IFNULL(
        (SELECT MAX(salary) 
         FROM Employee 
         WHERE salary NOT IN 
             (SELECT MAX(salary) FROM Employee)),
        NULL
    ) AS SecondHighestSalary

Complexity

  • Subquery with LIMIT and OFFSET:
    • Time: O(n log n) – sorting with limit (database-dependent, often optimized).
    • Space: O(1) – minimal extra space.
  • Subquery with NOT IN:
    • Time: O(n) – two passes, NOT IN may be slower.
    • Space: O(1) – minimal extra space.

Efficiency Notes

Subquery with LIMIT and OFFSET is the best solution with O(n log n) time (often optimized to O(n) with indexing) and O(1) space, offering clarity and efficiency—Subquery with NOT IN matches space but may be less efficient due to multiple scans and NOT IN, making it a viable but less preferred alternative.

Key Insights

  • LIMIT OFFSET: Direct ranking.
  • NOT IN: Exclusion-based.
  • IFNULL: Handles null cases.

Additional Example

Employee:
| 1 | 500 |
| 2 | 500 |
Output:
| null |
Explanation: Only 500, no second highest.

Edge Cases

  • Single Salary: [100] → null.
  • Duplicates: [100,100] → null.
  • Multiple Distinct: [100,200,300,400] → 300.

Both solutions handle these well.

Final Thoughts

LeetCode 176: Second Highest Salary in SQL is a classic ranking challenge. The Subquery with LIMIT and OFFSET solution excels with its efficiency and simplicity, while Subquery with NOT IN offers an alternative approach. Want more SQL? Try LeetCode 175: Combine Two Tables for joins or explore Python Basics for coding skills. Ready to practice? Solve LeetCode 176 on LeetCode with [100,200,300], aiming for 200—test your skills now!