Skip to content

Intermediate: combining and changing data · Subqueries, CTEs and set operations · topic 26 of 62 · 30 min

Scalar subqueries

Explanation

A subquery is a SELECT inside another query, written in brackets. A scalar subquery returns exactly one value (one row, one column), so you can use it anywhere a single value fits: in WHERE to compare with the average, or in SELECT to show the gap from the average on every row.

Why a query inside a query?

Some questions have two steps. "Who earns more than the company average?" needs the average first, and then the comparison. You could run two queries and type the first answer into the second, but the number goes stale the moment a salary changes. A subquery lets one query do both steps, always with fresh data.

A subquery (also called an inner query) is a complete SELECT written inside brackets within another statement, the outer query. A scalar subquery is one that returns a single value: one row with one column. "Scalar" just means "a single value", as opposed to a list or a table.

Think of it like this: Ask a friend "Is this phone cheaper than the average phone in the shop?" Your friend first finds the average (the inner question), then compares (the outer question). You asked one question; the answer needed a smaller question inside it.

In WHERE: compare with an aggregate

Module 4 showed that WHERE cannot contain AVG(salary) directly, because WHERE works on single rows before any grouping. A subquery solves that: the inner query computes the average over the whole table, and the outer WHERE compares each row with that one number.

sql
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Meera Iyer   | 250000
-- Rahul Sharma | 120000
-- Pooja Gupta  | 110000
-- Farhan Ali   | 105000
The average monthly salary in COMPANY is exactly 100000; four people earn more.
sql
SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);
-- Running Shoes | 2499
The most expensive product. If two products shared the top price, both would be returned, which ORDER BY ... LIMIT 1 would not do.

In SELECT: a value on every row

A scalar subquery can also be a column. It is computed for the result and repeated on each row, which is handy for comparisons with a total or an average.

sql
SELECT name, price,
       ROUND(price - (SELECT AVG(price) FROM products), 2) AS diff
FROM products;
-- Basmati Rice 5kg | 650  | -525.75
-- Running Shoes    | 2499 | 1323.25
-- ... (the average price is 1175.75)
sql
SELECT name, price,
       ROUND(100.0 * price / (SELECT SUM(price) FROM products), 1) AS pct
FROM products;
-- Running Shoes | 2499 | 26.6   <- 26.6% of the summed list prices
Multiply by 100.0, not 100, to avoid integer division.

What if the subquery returns zero or many rows?

Subquery returnsResult
One row, one columnThat value: the normal case
No rowsNULL. Comparing with NULL is unknown, so a WHERE using it keeps no rows
Many rowsPostgreSQL, MySQL and SQL Server raise an error ("more than one row returned by a subquery"); SQLite quietly uses the first row
Many columnsAn error everywhere: a scalar has one column
Careful: Because SQLite silently takes the first row, a subquery that should return one value but returns several can give a wrong answer here and an error on the production PostgreSQL server. Make sure a scalar subquery returns one value: use an aggregate (MAX, AVG, COUNT) or filter on a unique key.

Common mistakes

  • Writing WHERE salary > AVG(salary). Aggregates are not allowed in WHERE; wrap the average in a subquery.
  • Forgetting the brackets around the subquery, which is a syntax error.
  • A "scalar" subquery that returns several rows, such as (SELECT price FROM products WHERE category = 'Grocery'). Use = only with one value; use IN (next topic) for a list.
  • Using LIMIT 1 to find "the maximum" and missing ties. WHERE x = (SELECT MAX(x) ...) returns every tied row.

Need help?

Stuck on a word or an idea? Search this course, or every course, and jump straight to the explanation or a worked question.

Try:

Watch the lesson

Scalar subqueries · 1/6

Scalar subqueries

Some questions need two steps: find the average, then compare. A subquery does both in one query.

Prefer a video by a teacher? Find free videos on “sql scalar subquery where select above average tutorial” on YouTube ↗

In real life: Flagging unusual transactions

A simple first check in a bank or payment app is to flag payments much larger than usual, for example more than one and a half times the average amount. The average is a scalar subquery, so the rule keeps working as new payments arrive. Real fraud systems use many more signals, but this "compare with an aggregate" pattern is the starting point of many reports.

sql
SELECT order_id, product_id, qty * price AS amount
FROM order_items
WHERE qty * price > 1.5 * (SELECT AVG(qty * price) FROM order_items);
The average line is about 1362, so lines above about 2043 are flagged: the two Running Shoes lines (2499 each).

Practice (0/4 done)

Task 1 · Code · SQL

Which employees earn more than the company average salary? Return name and salary. Use a subquery for the average.

Task 2 · Code · SQL

Return the name and price of the most expensive product, using a subquery with MAX (so that ties would all be returned).

Task 3 · Code · SQL

For every product, return name, price and diff: the price minus the average price of all products, rounded to 2 decimal places.

Task 4 · Code · SQL

How far behind the top scorer is each student? For students who have marks, return name, marks and behind (the highest marks in the class minus the student's marks).

Quick quiz

Question 1

1.Why does WHERE salary > AVG(salary) fail, while WHERE salary > (SELECT AVG(salary) FROM employees) works?

Question 2

2.A scalar subquery returns no rows. What value does it give?

Question 3

3.Two products share the highest price. Which query returns both?

Question 4

4.In PostgreSQL, a subquery used with = returns 3 rows. What happens?

Question 5

5.What does "scalar" mean in "scalar subquery"?

Read more (free)

Open a question to see the answer and why. The first ones are this topic's own quiz, so try the quiz above before you peek.

Why does WHERE salary > AVG(salary) fail, while WHERE salary > (SELECT AVG(salary) FROM employees) works?

Answer: WHERE works row by row and cannot use aggregates; the subquery computes the average separately as one value

The subquery is a complete query that returns one number.

A scalar subquery returns no rows. What value does it give?

Answer: NULL

An empty scalar subquery gives NULL.

Two products share the highest price. Which query returns both?

Answer: WHERE price = (SELECT MAX(price) FROM products)

Every row equal to the maximum passes the WHERE.

In PostgreSQL, a subquery used with = returns 3 rows. What happens?

Answer: An error: more than one row returned by a subquery

PostgreSQL, MySQL and SQL Server raise an error; SQLite quietly uses the first row.

What does "scalar" mean in "scalar subquery"?

Answer: It returns a single value: one row and one column

A scalar is one value, so it fits anywhere a single value fits.

Inside WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = dept_id), what does dept_id = dept_id compare?Related

Answer: The inner row with itself, so it is always true (except for NULLs)

Unqualified names refer to the nearest table. Use aliases: e2.dept_id = e.dept_id.

From Correlated subqueries and derived tables

What does WHERE price > (SELECT AVG(price) FROM products) do?Related

Answer: Keeps products whose price is above the overall average

The subquery returns one number, the average; the outer WHERE compares each price with it.

From Subqueries, CTEs and window functions

The subquery SELECT dept_id FROM employees returns 1, 2, 3, 4 and one NULL. How many rows does WHERE id NOT IN (that subquery) return from departments (ids 1 to 5)?Related

Answer: 0

id <> NULL is unknown, so the NOT IN is never true for any row.

From IN, EXISTS and NOT EXISTS

No product is in the Toys category. What does SELECT SUM(price) FROM products WHERE category = 'Toys' return?Related

Answer: NULL

SUM over no rows is NULL (COUNT would be 0). Use COALESCE(SUM(price), 0) to show 0.

From Aggregate functions

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: IN, EXISTS and NOT EXISTS →

Next steps

  1. 5 more topics in “Subqueries, CTEs and set operations” before its exam: IN, EXISTS and NOT EXISTS, Correlated subqueries and derived tables, Common table expressions (WITH), Recursive CTEs, UNION, INTERSECT and EXCEPT.
  2. Module 6 exam: subqueries, CTEs and set operations · 14 questions · 25 min, without help. 6 topics of this module still to finish first.
  3. Then the next module, Window functions. 62 topics left in the course before the final exam.

← Previous: Join pitfalls and how to avoid them