Subqueries and Joins for Departmental Filtering

This chapter explores essential SQL query patterns frequently encountered in technical interviews. It covers advanced filtering techniques using window functions, handling case sensitivity in string matching, executing self-joins to compare hierarchical data, understanding the core mechanics of SQL joins, and performing atomic data updates using conditional CASE expressions.

1. The Challenge of Departmental Filtering (Second Highest Salary)

A classic SQL interview problem is finding the $N$-th highest salary within each department. While finding the global maximum is straightforward using MAX(), finding departmental offsets (such as the second highest salary) requires partitioning the dataset.

The Problem Scenario

Consider an employee table containing employee_id, department_id, and salary. We need to retrieve the records of employees who earn the exact second highest salary within their respective departments.

During evaluation, we must account for three critical edge cases:

• Ties: Multiple employees in the same department earning the exact same second highest salary.
• Single-Employee Departments: Departments with only one employee (where no second highest salary exists).
• No Data: Departments with no employees.

2. Step-by-Step SQL Implementation for Departmental Filtering

To solve this efficiently, we use a window function—such as DENSE_RANK() or ROW_NUMBER()—inside a Common Table Expression (CTE) or subquery, and then filter the ranked results in the outer query.

The Naive vs. Window Function Approach

• Naive Approach: Using correlated subqueries with LIMIT or OFFSET. This is highly inefficient, costing $O(N^2)$ in the worst case, and fails to partition cleanly by department.
• Window Function Approach: Computes ranks dynamically in a single pass over the partitioned data, running in $O(N \log N)$ time due to sorting within partitions.

SQL Implementation

WITH RankedSalaries AS (
    SELECT 
        employee_id,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) AS rn
    FROM employees
)
SELECT 
    employee_id,
    department_id,
    salary
FROM RankedSalaries
WHERE rn = 2;

Behavior Analysis by Department

• Department 100: Contains two employees sharing the second highest salary. Because DENSE_RANK() assigns the same rank to duplicate values, both records are returned (rn = 2).
• Department 200: Contains a distinct second highest salary. A single record is returned.
• Department 900: Contains only one employee. The maximum rank assigned is 1. Since no record matches rn = 2, this department is omitted from the final output.

3. Case-Insensitivity in String Matching

When filtering text data, case sensitivity can lead to silent data omissions. For example, searching for transactions associated with the customer name "Shilpa" might fail if the database stores variations like "SHILPA", "shilpa", or "ShilPa".

The Collation Trap

Some database engines (like SQL Server with default collations) are case-insensitive by default. However, standard ANSI SQL databases (such as PostgreSQL or Oracle) are case-sensitive. Relying on default database settings makes queries non-portable and fragile.

The Solution: Standardizing Case

To guarantee that a query returns all matching records regardless of how they are capitalized in the database, apply the UPPER() or LOWER() function to both the column and the search term.

-- Robust, case-insensitive search
SELECT * 
FROM transactions
WHERE UPPER(customer_name) = 'SHILPA';

Note: Applying functions like UPPER() directly to a column prevents the database engine from using standard B-Tree indexes on that column (making the query non-sargable). If performance is critical on large datasets, consider using a functional index (expression index) or a case-insensitive collation.

4. Comparing Employee and Manager Salaries (Self-Joins)

A common relational database pattern is the Adjacency List, where a table references itself. In an employee table, this is represented by a manager_id column pointing to the employee_id of the manager.

To find all employees who earn more than their direct managers, we must perform a Self-Join. This treats a single physical table as two distinct logical tables: one representing employees (e) and the other representing managers (m).

SQL Implementation

SELECT 
    e.employee_id AS employee_id,
    e.salary AS employee_salary,
    m.employee_id AS manager_id,
    m.salary AS manager_salary
FROM employees e
INNER JOIN employees m 
    ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

5. Visualizing Self-Joins

The self-join maps the hierarchical relationship by aligning the employee's manager pointer to the manager's primary record:

6. Core SQL Joins Explained

During interviews, candidates are frequently asked to explain or write out the results of different join types. The table below summarizes the behavior of the four primary join types:

7. The Value Swapping Dilemma (The Gender Swap Problem)

Consider an orders table where the values in the customer_gender column were accidentally reversed: female customers are labeled as 'Male', and male customers are labeled as 'Female'. We need to write an update query to correct this.

Why Naive Sequential Updates Fail

A naive approach attempts to update the values using two sequential queries:

-- Step 1: Update Males to Females
UPDATE orders 
SET customer_gender = 'Female' 
WHERE customer_gender = 'Male';

-- Step 2: Update Females to Males
UPDATE orders 
SET customer_gender = 'Male' 
WHERE customer_gender = 'Female';

The Destructive Overwrite Flow

This sequential approach destroys the data state during the first step:

Because SQL executes statements sequentially, the first update overwrites the target values, making it impossible for the second update to identify which records were originally female.

8. Atomic Updates Using CASE Expressions

To solve the swapping dilemma, we must execute both updates atomically in a single pass. This is achieved by using a conditional CASE expression within a single UPDATE statement.

SQL Implementation

UPDATE orders
SET customer_gender = CASE 
    WHEN customer_gender = 'Male' THEN 'Female'
    WHEN customer_gender = 'Female' THEN 'Male'
    ELSE customer_gender -- Safeguard to preserve other values (e.g., NULL or Non-binary)
END;

How It Works

The database engine evaluates the CASE expression for each row individually on its current state before writing the changes back to disk. This prevents the intermediate state loss caused by sequential queries.

9. Summary and Key Takeaways

• Window Functions for Ranking: Use DENSE_RANK() inside a subquery or CTE to find the $N$-th highest value per group. This approach cleanly handles ties and missing data.
• Case-Insensitive Queries: Use UPPER(column) = 'VALUE' to ensure text searches are reliable across different database engines.
• Self-Joins for Hierarchies: Treat a single table as two logical tables (employees and managers) to compare parent-child relationships within the same dataset.
• Join Behaviors: Master the differences between INNER, LEFT, RIGHT, and FULL OUTER joins, as interviewers frequently test these using sample datasets.
• Atomic Updates with CASE: Avoid sequential UPDATE statements when swapping values. Use a single UPDATE with a CASE expression to modify values in one pass and prevent data loss.