Scalar Subqueries
Overview
A Subquery is simply a query nested inside another query. A 'Scalar' Subquery is a specific type that returns exactly one single value (one row, one column). It acts as a dynamic variable. For example, instead of writing WHERE salary > 50000 (hardcoding the number), you can write WHERE salary > (SELECT AVG(salary) FROM employees). The database runs the inner query first, calculates the average, and then injects that number into the outer query.
Syntax
-- 1. In the WHERE clause (Dynamic Filtering)
SELECT first_name, salary
FROM employees
-- The inner query executes first, returning a single number!
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 2. In the SELECT clause (Inline calculation)
SELECT
product_name,
price,
-- This calculates the max price across the ENTIRE table
-- and attaches it to every single row for comparison!
(SELECT MAX(price) FROM products) AS most_expensive_item
FROM products;Common Pitfalls
- Returning multiple rows from a Scalar subquery. If you write
WHERE salary > (SELECT salary FROM employees), the inner query returns 10,000 different salaries. The database will crash with 'Subquery returns more than 1 row'. A Scalar subquery MUST use an aggregate function or a strictLIMIT 1filter to guarantee a single value. - Overusing them in the
SELECTclause. If you put a subquery in theSELECTlist, the database might execute it once for every single row in the outer table, causing massive performance degradation. (Modern optimizers often fix this, but JOINS are usually safer).
Interview Questions
NULL value? If so, what happens to the outer query?Yes. If the inner query returns no data (e.g., SELECT MAX(salary) FROM emp WHERE dept='Fake'), it evaluates to NULL. The outer query then becomes WHERE salary > NULL, which evaluates to Unknown/False, causing the outer query to return zero rows.
Real-World Example
Finding the latest order placed by ANY user in the database.
SELECT user_id, total_amount, order_date
FROM orders
-- Dynamically finds the absolute maximum date in the entire table
WHERE order_date = (SELECT MAX(order_date) FROM orders);Check Your Knowledge
Test your understanding of Scalar Subqueries with these quick questions.