Correlated subqueries for beginners (step by step walkthrough to find Nth highest salary in sql)

 

Correlated Subqueries

Let's explore correlated subqueries with the example of finding the 3rd highest salary among 5 employees. A correlated subquery is a subquery that references columns from the outer query. It executes once for each row processed by the outer query.

Example Table: Employees

ID	Name	Salary
1	Alice	9000
2	Bob	8000
3	Carol	8500
4	Dave	7000
5	Eve	9500

Query to Find the 3rd Highest Salary

    
    SELECT Salary
    FROM Employees AS E1
    WHERE 2 = (
        SELECT COUNT(DISTINCT Salary)
        FROM Employees AS E2
        WHERE E2.Salary > E1.Salary
    )
    
    

Step-by-Step Walkthrough with Diagram

1. Outer Query (E1):

               
               SELECT Salary
               FROM Employees AS E1
               
               

   This part selects the salary from the outer query's table (E1).

2. Inner Query (E2):

               
               SELECT COUNT(DISTINCT Salary)
               FROM Employees AS E2
               WHERE E2.Salary > E1.Salary
               
               

   The inner query counts distinct salaries in the inner table (E2) that are greater than the current salary in the outer table (E1).

3. Condition:

               
               WHERE 2 = (
                   SELECT COUNT(DISTINCT Salary)
                   FROM Employees AS E2
                   WHERE E2.Salary > E1.Salary
               )
               
               

   The condition checks if the count of distinct salaries greater than the current salary (E1.Salary) is equal to 2. This means the current salary should be the 3rd highest.

Comparisons for Each Row with Diagram

• Row with Alice (Salary = 9000):

              
              +----+-------+--------+
              | ID | Name  | Salary |
              +----+-------+--------+
              | 1  | Alice | 9000   |
              +----+-------+--------+
              
              

  Inner Query:

              
              SELECT COUNT(DISTINCT Salary)
              FROM Employees AS E2
              WHERE E2.Salary > 9000
              
              

  Inner Query Result: 1 (only Eve's salary 9500 is greater)
  Condition: 2 != 1 (not satisfied)

              
              +-----------------------+
              | E2.Salary > E1.Salary |
              +-----------------------+
              | Eve's Salary (9500)   |
              +-----------------------+
              | Result: 1             |
              +-----------------------+
              
              

• Row with Bob (Salary = 8000):

              
              +----+-------+--------+
              | ID | Name  | Salary |
              +----+-------+--------+
              | 2  | Bob   | 8000   |
              +----+-------+--------+
              
              

  Inner Query:

              
              SELECT COUNT(DISTINCT Salary)
              FROM Employees AS E2
              WHERE E2.Salary > 8000
              
              

  Inner Query Result: 3 (Alice's, Carol's, and Eve's salaries are greater)
  Condition: 2 != 3 (not satisfied)

              
              +-----------------------+
              | E2.Salary > E1.Salary |
              +-----------------------+
              | Alice's Salary (9000) |
              | Carol's Salary (8500) |
              | Eve's Salary (9500)   |
              +-----------------------+
              | Result: 3             |
              +-----------------------+
              
              

• Row with Carol (Salary = 8500):

              
              +----+-------+--------+
              | ID | Name  | Salary |
              +----+-------+--------+
              | 3  | Carol | 8500   |
              +----+-------+--------+
              
              

  Inner Query:

              
              SELECT COUNT(DISTINCT Salary)
              FROM Employees AS E2
              WHERE E2.Salary > 8500
              
              

  Inner Query Result: 2 (Alice's and Eve's salaries are greater)
  Condition: 2 = 2 (satisfied)

              
              +-----------------------+
              | E2.Salary > E1.Salary |
              +-----------------------+
              | Alice's Salary (9000) |
              | Eve's Salary (9500)   |
              +-----------------------+
              | Result: 2             |
              +-----------------------+
              
              

• Row with Dave (Salary = 7000):

              
              +----+-------+--------+
              | ID | Name  | Salary |
              +----+-------+--------+
              | 4  | Dave  | 7000   |
              +----+-------+--------+
              
              

  Inner Query:

              
              SELECT COUNT(DISTINCT Salary)
              FROM Employees AS E2
              WHERE E2.Salary > 7000
              
              

  Inner Query Result: 4 (Alice's, Bob's, Carol's, and Eve's salaries are greater)
  Condition: 2 != 4 (not satisfied)

              
              +-----------------------+
              | E2.Salary > E1.Salary |
              +-----------------------+
              | Alice's Salary (9000) |
              | Bob's Salary (8000)   |
              | Carol's Salary (8500) |
              | Eve's Salary (9500)   |
              +-----------------------+
              | Result: 4             |
              +-----------------------+
              
              

• Row with Eve (Salary = 9500):

              
              +----+-------+--------+
              | ID | Name  | Salary |
              +----+-------+--------+
              | 5  | Eve   | 9500   |
              +----+-------+--------+
              
              

  Inner Query:

              
              SELECT COUNT(DISTINCT Salary)
              FROM Employees AS E2
              WHERE E2.Salary > 9500
              
              

  Inner Query Result: 0 (no salaries are greater)
  Condition: 2 != 0 (not satisfied)

              
              +-----------------------+
              | E2.Salary > E1.Salary |
              +-----------------------+
              | None                  |
              +-----------------------+
              | Result: 0             |
              +-----------------------+
              
              

Final Result

The 3rd highest salary is 8500 (from Carol).

Support Our Efforts and Earn Together 🚀

Visit https://parucodes.github.io/ today and start your journey to becoming a fast, accurate, and confident touch typist.

If you find our website useful and want to support us, consider joining the exciting world of Bitcoin mining on your mobile phone. Follow this link: Mine PI Bitcoin and use my username prarthanadp as your invitation code. With the referral code prarthanadp, you'll receive a special referral bonus.

Thank you for your support! Let's grow and earn together! 🌟