Scalar Subqueries

A subquery is a query nested inside another query. The inner query runs first, and its result is used by the outer query. Think of it as solving a problem in steps: compute something first, then use that result.

A scalar subquery is the simplest kind -- it returns exactly one value (one row, one column). This makes it usable anywhere a single value is expected.

Why Subqueries?

Consider this question: "Which employees earn more than the average salary?"

You cannot write this:

-- THIS DOES NOT WORK!
SELECT name, salary
FROM employees
WHERE salary > AVG(salary);

PostgreSQL will raise an error because aggregate functions like AVG() are not allowed in the WHERE clause. The solution is a subquery:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The subquery (SELECT AVG(salary) FROM employees) runs first and returns a single number (the average salary). Then the outer query compares each employee's salary against that number.

Scalar Subqueries in the WHERE Clause

Comparing to an Aggregate

Find employees who earn more than the average:

SELECT name, salary, department
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Comparing to a Specific Value

Find employees who earn as much as or more than the highest-paid person in Marketing:

SELECT name, salary, department
FROM employees
WHERE salary >= (
  SELECT MAX(salary)
  FROM employees
  WHERE department = 'Marketing'
);

Finding the Employee with the Highest Salary

SELECT name, salary, department
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Scalar Subqueries in the SELECT List

You can use a scalar subquery as a computed column. The subquery is evaluated once (or once per row for correlated subqueries, covered later) and its value appears in the output:

SELECT
  name,
  salary,
  (SELECT AVG(salary) FROM employees) AS avg_salary,
  salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees
ORDER BY diff_from_avg DESC;

This adds two computed columns: the company-wide average salary and how far each employee is from that average.

Comparing to Department Average

SELECT
  name,
  department,
  salary,
  (SELECT ROUND(AVG(salary)) FROM employees) AS company_avg
FROM employees
ORDER BY salary DESC;

Scalar Subqueries with Products

Find products that are priced above average:

SELECT name, category, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Find the cheapest product:

SELECT name, price
FROM products
WHERE price = (SELECT MIN(price) FROM products);

Show each product alongside the most expensive product's price for comparison:

SELECT
  name,
  price,
  (SELECT MAX(price) FROM products) AS max_price,
  (SELECT MAX(price) FROM products) - price AS savings_vs_max
FROM products
ORDER BY price;

Using Scalar Subqueries in Conditions

In CASE Expressions

SELECT
  name,
  salary,
  CASE
    WHEN salary > (SELECT AVG(salary) FROM employees) THEN 'Above average'
    WHEN salary = (SELECT AVG(salary) FROM employees) THEN 'At average'
    ELSE 'Below average'
  END AS salary_category
FROM employees
ORDER BY salary DESC;

With Mathematical Operators

SELECT
  name,
  salary,
  ROUND(salary * 100.0 / (SELECT SUM(salary) FROM employees), 1) AS pct_of_total
FROM employees
ORDER BY pct_of_total DESC;

This calculates what percentage of the total salary bill each employee represents.

Rules for Scalar Subqueries

1. Must return exactly one column. If your subquery selects multiple columns, it cannot be used as a scalar subquery.

2. Must return zero or one row. If it returns zero rows, the result is NULL. If it returns more than one row, PostgreSQL raises an error.

3. Wrapped in parentheses. All subqueries are enclosed in ( ).

4. Can reference different tables. The inner query can query a completely different table than the outer query.

Common Mistakes

Subquery Returns Multiple Rows

-- THIS WILL FAIL if more than one employee has the same max salary:
SELECT name FROM employees
WHERE salary = (SELECT salary FROM employees WHERE department = 'Engineering');
-- ERROR: more than one row returned

-- Fix: use an aggregate to guarantee one row:
SELECT name FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE department = 'Engineering');

Forgetting Parentheses

-- WRONG:
SELECT name FROM employees WHERE salary > SELECT AVG(salary) FROM employees;

-- CORRECT:
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Try It Yourself

1. Find all employees whose salary is below the company average. Display their name, salary, and the average salary for comparison.

2. Find products that cost more than double the average product price.

3. For each employee, show their name, salary, and a column indicating whether they are the highest-paid employee ('Yes' or 'No') using a scalar subquery in a CASE expression.

4. Calculate what percentage of the total product inventory value each product represents (price * quantity as a percentage of the total sum of price * quantity).