Subquery in CASE Expression
Subquery in CASE Expression is a SQL statement in the Subqueries category. Subqueries can be used inside CASE expressions to compute dynamic conditions. The syntax is CASE WHEN (SELECT COUNT(*) FROM t2) > 0 THEN ... ELSE ... END. It returns dynamic CASE result. A typical example: SELECT name, salary, CASE WHEN salary > (SELECT AVG(salary) FROM employees) THEN 'Above Average' WHEN salary = (SELECT AVG(salary) FROM employees) THEN 'Average' ELSE 'Below Average' END AS salary_category FROM employees; -- Each row compared to company average SELECT name, CASE department WHEN (SELECT id FROM departments WHERE name = 'IT') THEN 'Technology' WHEN (SELECT id FROM departments WHERE name = 'HR') THEN 'People' ELSE 'Other' END AS category FROM… A close relative is Scalar Subquery, which a subquery that returns a single value (one row, one column). Can be used in SELECT, WHERE, HAVING clauses. A close relative is Correlated Subquery, which a subquery that references columns from the outer query. Evaluated for each row of the outer query.