0Pricing
SQL Interview Prep · Lesson

Scalar Subqueries in SELECT and WHERE

Single-value subqueries and the error when they return more than one row.

Scalar Subqueries in SELECT and WHERE is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What an Interviewer Means by Scalar Subquery

A scalar subquery is a query that returns exactly one row and one column — a single value. Because it resolves to one value, SQL lets you drop it almost anywhere a literal could go: in SELECT, WHERE, HAVING, even ORDER BY.

  • Interviewers test whether you know the one row, one column rule.
  • The classic trap: a subquery that accidentally returns more than one row.

If you can state that definition cleanly, you have already passed the first checkpoint.

A Scalar Subquery in the SELECT List

Putting a scalar subquery in the SELECT list lets you attach a computed single value to every output row. Here we show each employee next to the company-wide average salary.

The subquery (SELECT AVG(salary) FROM employees) runs and collapses the whole table to one number, then that number is repeated on every row.

SELECT
  name,
  salary,
  (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

A Scalar Subquery in WHERE

The same single value can drive a filter. A very common interview ask is find everyone who earns more than the company average.

The subquery computes the average once, then each row in the outer query is compared against it. This is not correlated — the inner query does not depend on the outer row, so it runs a single time.

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

The 'More Than One Row' Error

This is the error interviewers want you to anticipate. If your subquery is used with = or > but returns multiple rows, the engine throws:

  • Postgres: more than one row returned by a subquery used as an expression
  • MySQL: Subquery returns more than 1 row

The query below fails because there may be several employees in department 5 — the subquery is not scalar.

SELECT name
FROM employees
WHERE salary = (SELECT salary FROM employees WHERE dept_id = 5);

Forcing a Subquery to Be Scalar

Two reliable ways to guarantee a single value:

  • Use an aggregate like MAX, MIN, or AVG — aggregates without GROUP BY always return one row.
  • Use LIMIT 1 (Postgres/MySQL) or FETCH FIRST 1 ROW ONLY after an ORDER BY.

The fixed version below asks for the single highest salary in department 5.

SELECT name
FROM employees
WHERE salary = (
  SELECT MAX(salary) FROM employees WHERE dept_id = 5
);

Scalar Subqueries Return NULL on No Rows

A subtle interview point: if a scalar subquery matches zero rows, it does not error — it returns NULL. That NULL then propagates through your comparison.

Because salary > NULL evaluates to UNKNOWN (not true), the outer query returns no rows. Candidates often expect an error here; the correct answer is empty result, silently.

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

Worked Example: Above-Average Earners With the Gap

Let's combine both placements. We show each above-average earner and how far above the average they sit. The same scalar subquery appears in SELECT and WHERE.

An interviewer may ask whether the subquery runs twice. Logically it appears twice, but a good optimizer can evaluate the uncorrelated subquery a single time and reuse it.

SELECT
  name,
  salary,
  salary - (SELECT AVG(salary) FROM employees) AS above_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY above_avg DESC;

Scalar Subquery in ORDER BY

Because a scalar subquery is just a value, it is also legal in ORDER BY. This is rarely the cleanest approach, but interviewers like to confirm you know it is allowed.

Here we sort departments by a value pulled from another table — each department's employee count — without joining.

SELECT d.dept_name
FROM departments d
ORDER BY (
  SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.id
) DESC;

Scalar vs Correlated: Knowing the Line

The ORDER BY example above secretly references d.id from the outer query — that makes it a correlated scalar subquery, running once per outer row.

Interviewers love this distinction:

  • Uncorrelated scalar subquery: self-contained, runs once.
  • Correlated scalar subquery: references the outer row, runs per row.

Both are still scalar (one value), but performance differs dramatically.

When NOT to Use a Scalar Subquery

A correlated scalar subquery in the SELECT list is convenient but can be slow on large tables — it executes per row. Interviewers expect you to know the alternatives:

  • A LEFT JOIN to a pre-aggregated derived table.
  • A window function such as AVG(salary) OVER ().

The window form below produces the same company average column without a separate subquery scan.

SELECT
  name,
  salary,
  AVG(salary) OVER () AS company_avg
FROM employees;

Interview Soundbite

If asked to define a scalar subquery, say this: "A scalar subquery returns one row and one column, so it acts like a single value and can be used anywhere a literal is allowed. If it returns more than one row the engine errors; if it returns no rows it yields NULL."

That single sentence covers the definition, the error case, and the NULL edge case — the three things every interviewer is listening for.

Quick Check

Test your understanding of scalar subquery behavior.

Recap

You now own scalar subqueries:

  • Definition: one row, one column — usable like a literal in SELECT, WHERE, HAVING, and ORDER BY.
  • Multiple rows with =/> cause an error; force scalarity with an aggregate or LIMIT 1.
  • Zero rows yield NULL, which silently drops rows in a filter.
  • Correlated scalar subqueries run per row; prefer joins or window functions when performance matters.

Next: subqueries in the FROM clause, where the result is a whole virtual table.

Frequently asked questions

Is the “Scalar Subqueries in SELECT and WHERE” lesson free?

Yes — the full text of “Scalar Subqueries in SELECT and WHERE” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Scalar Subqueries in SELECT and WHERE”?

Single-value subqueries and the error when they return more than one row. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Scalar Subqueries in SELECT and WHERE” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Scalar Subqueries in SELECT and WHERE
  2. Subqueries in the FROM Clause (Derived Tables)
  3. IN, ANY and ALL Subqueries
  4. EXISTS vs IN Performance
← Back to SQL Interview Prep