Chapter 9: Conditional Logic (CASE), Subqueries & Set Operations

Welcome to Chapter 9. As your applications grow in complexity, simple filtering and joining are no longer enough. You often need to introduce conditional branching (if-else logic), evaluate subqueries, and calculate analytical rankings.

In this chapter, we explore the CASE expression, dissect uncorrelated and correlated subqueries, and solve the classic Nth Highest Salary challenge using both traditional SQL and modern MySQL 8.0+ window functions.

💡 9.1 Conditional Evaluation with CASE

The CASE expression allows you to implement conditional logic directly inside your SQL statements. It evaluates conditions sequentially and returns the value specified in the first true WHEN branch:

-- Segment employees into Low, Mid, and High salary tiers
SELECT 
    CONCAT(first_name, ' ', last_name) AS employee,
    salary,
    CASE
        WHEN salary >= 2000 AND salary < 5000 THEN 'low' 
        WHEN salary >= 5000 AND salary < 10000 THEN 'mid'
        ELSE 'high'
    END AS salary_level
FROM employees
ORDER BY salary ASC;

⚠ SQL Standards Notice: Always enclose string literals in single quotes ('low'), not double quotes ("low"). Under strict ANSI SQL mode, double quotes are interpreted as database column identifiers!

🔮 9.2 Subqueries: Uncorrelated vs Correlated

A. Uncorrelated Subquery

An uncorrelated subquery does not depend on the outer query. It executes exactly once, and its result is handed to the outer query:

-- Find all employees who earn strictly more than the company-wide average
SELECT 
    CONCAT(first_name, ' ', last_name) AS name,
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

B. Correlated Subquery (The Nth Highest Salary Problem)

A correlated subquery references columns from the outer query. The database engine re-evaluates the inner subquery for every candidate row evaluated by the outer query:

-- Set variable for desired rank (e.g. 3 for 3rd highest salary)
SET @input := 3;

SELECT employee_id, first_name, last_name, job_id, salary 
FROM employees e 
WHERE @input = (
    SELECT COUNT(DISTINCT p.salary) 
    FROM employees p 
    WHERE p.salary >= e.salary
);

🎓 9.3 Modern Alternative: MySQL 8.0+ Window Functions

In modern MySQL 8.0 and MariaDB 10.2+, window functions provide superior readability and execution efficiency compared to correlated subqueries:

-- Solving Nth highest salary using DENSE_RANK()
WITH RankedSalaries AS (
    SELECT 
        employee_id, 
        first_name, 
        last_name, 
        job_id, 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT employee_id, first_name, last_name, job_id, salary
FROM RankedSalaries
WHERE rnk = 3;

❓ Frequently Asked Questions (FAQ)

1. What is the difference between RANK() and DENSE_RANK()?

If two employees tie for 1st place with identical salaries, RANK() assigns them both rank 1 and assigns the next employee rank 3 (leaving a gap). DENSE_RANK() assigns them both rank 1 and assigns the next employee rank 2 (leaving no gaps).

2. Can a CASE expression be used inside an ORDER BY clause?

Yes! This is known as custom sorting. You can write: ORDER BY CASE WHEN job_id = 'AD_PRES' THEN 1 ELSE 2 END.