Scalar subqueries in MySQL - Deep Dive

Overview - Scalar subqueries

What is it?

A scalar subquery is a small query inside another query that returns exactly one value. It acts like a single piece of data, such as a number or text, which can be used in places where a single value is expected. This helps to compare or calculate values dynamically based on other data in the database. Scalar subqueries are useful when you want to use the result of one query inside another query.

Why it matters

Without scalar subqueries, you would have to run multiple separate queries and manually combine their results outside the database, which is slow and error-prone. Scalar subqueries let the database do this work efficiently and safely in one step. This makes your queries simpler, faster, and easier to maintain. It also allows you to write powerful queries that adapt to changing data automatically.

Where it fits

Before learning scalar subqueries, you should understand basic SQL queries, SELECT statements, and how to filter data with WHERE clauses. After mastering scalar subqueries, you can learn about more complex subqueries like correlated subqueries, joins, and set operations to handle advanced data retrieval tasks.

Mental Model

Core Idea

A scalar subquery is like a tiny question inside a bigger question that returns one answer to be used immediately.

Think of it like...

Imagine you are baking a cake and need to know how many eggs you have left. Instead of counting all eggs separately, you quickly check the egg carton (the small question) and use that number right away in your recipe (the big question).

Main Query
  │
  ├─ Uses value from
  │    ┌───────────────┐
  │    │ Scalar Subquery│
  │    │  (returns 1)  │
  │    └───────────────┘
  │
  └─ Continues processing with that value

Build-Up - 6 Steps

1

Foundation

Understanding basic subqueries

Concept: Learn what a subquery is and how it fits inside a main query.

A subquery is a query nested inside another query. It can return multiple rows or a single value. For example, you can write SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales'); Here, the subquery finds the department id for 'Sales', and the main query finds employees in that department.

Result

The query returns all employees who work in the Sales department.

Knowing that subqueries can be used inside other queries opens up flexible ways to filter and retrieve data.

2

Foundation

What makes a subquery scalar?

Concept: A scalar subquery returns exactly one value (one row, one column).

A scalar subquery must return a single value to be used where one value is expected, like in a WHERE clause or SELECT list. For example: SELECT name, (SELECT MAX(salary) FROM employees) AS max_salary FROM employees; The subquery returns one number, the highest salary, used for each row.

Result

Each employee's name is listed along with the highest salary in the company.

Understanding the single-value rule for scalar subqueries helps avoid errors and misuse.

3

Intermediate

Using scalar subqueries in SELECT clause

🤔Before reading on: do you think a scalar subquery in SELECT runs once or once per row? Commit to your answer.

Concept: Scalar subqueries in SELECT can run once or once per row depending on whether they depend on the outer query.

If the scalar subquery does not depend on the outer query, it runs once and the value is reused. If it depends on outer query columns, it runs for each row. Example without dependency: SELECT name, (SELECT COUNT(*) FROM employees) AS total_employees FROM employees; Example with dependency: SELECT name, (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) AS max_dept_salary FROM employees e1;

Result

The first query shows each employee's name with total employees repeated. The second shows max salary per employee's department.

Knowing when scalar subqueries run helps optimize queries and understand performance.

4

Intermediate

Scalar subqueries in WHERE clause

🤔Before reading on: can a scalar subquery in WHERE compare to multiple rows? Commit to yes or no.

Concept: Scalar subqueries in WHERE must return one value to compare with outer query values.

You can use scalar subqueries to filter rows based on a single value. For example: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); This returns employees earning more than the average salary. If the subquery returns more than one value, the query will error.

Result

Only employees with salary above average are returned.

Understanding the single-value requirement prevents common errors and clarifies query logic.

5

Advanced

Handling errors with scalar subqueries

🤔Before reading on: what happens if a scalar subquery returns zero or multiple rows? Predict the outcome.

Concept: Scalar subqueries must return exactly one value; otherwise, errors occur.

If a scalar subquery returns no rows, it returns NULL, which can affect comparisons. If it returns multiple rows, MySQL throws an error like 'Subquery returns more than 1 row'. To avoid this, use aggregation functions like MAX, MIN, or LIMIT 1. Example: SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales' LIMIT 1);

Result

The query runs safely without errors, returning employees in Sales department.

Knowing how to handle subquery results prevents runtime errors and ensures query reliability.

6

Expert

Performance considerations and optimization

🤔Before reading on: do you think scalar subqueries always run once or can they run multiple times? Commit to your answer.

Concept: Scalar subqueries can cause performance issues if executed repeatedly; understanding execution helps optimize queries.

When scalar subqueries depend on outer query rows, they run once per row, which can be slow on large data. MySQL may cache results for non-dependent subqueries. To optimize, rewrite queries using JOINs or apply indexing. Example: Instead of SELECT name, (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) FROM employees e1; consider JOIN with grouped max salaries. EXPLAIN can show query plans to identify slow parts.

Result

Optimized queries run faster and use fewer resources.

Understanding execution frequency of scalar subqueries helps write efficient, scalable SQL.

Under the Hood

When MySQL executes a scalar subquery, it runs the inner query first to get a single value. If the subquery depends on the outer query, it runs once for each outer row. The database engine manages memory and temporary storage to hold subquery results. If the subquery returns multiple rows where one is expected, MySQL raises an error. Internally, scalar subqueries are treated as expressions that produce a single value to be used in the main query's evaluation.

Why designed this way?

Scalar subqueries were designed to allow flexible, readable queries that combine multiple steps into one. The single-value rule simplifies query logic and prevents ambiguity. Alternatives like joins can be more complex or less intuitive in some cases. The design balances expressiveness with clear rules to avoid errors and maintain performance.

┌─────────────────────────────┐
│        Main Query           │
│  ┌───────────────────────┐  │
│  │ Scalar Subquery runs   │  │
│  │ 1 or many times based  │  │
│  │ on dependency          │  │
│  └─────────┬─────────────┘  │
│            │                │
│  Uses single value result   │
└────────────┴────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a scalar subquery always return exactly one row? Commit yes or no.

Common Belief: A scalar subquery always returns exactly one row and one column.

Reality: A scalar subquery must return one column but can return zero rows, which results in NULL, or multiple rows, which causes an error.

Why it matters: Assuming it always returns one row can lead to unexpected NULL results or runtime errors that break applications.

Quick: Can scalar subqueries be replaced by JOINs in all cases? Commit yes or no.

Common Belief: Scalar subqueries and JOINs are interchangeable and always perform the same.

Reality: While sometimes interchangeable, scalar subqueries and JOINs have different performance and readability tradeoffs. JOINs can be more efficient but more complex to write.

Why it matters: Blindly replacing scalar subqueries with JOINs can cause slower queries or harder-to-maintain code.

Quick: Does a scalar subquery in SELECT run once for the whole query? Commit yes or no.

Common Belief: Scalar subqueries in SELECT always run once and reuse the result for all rows.

Reality: If the scalar subquery depends on outer query columns, it runs once per row, which can be costly.

Why it matters: Not knowing this can cause unexpected slow queries and resource use.

Quick: Can a scalar subquery return multiple columns? Commit yes or no.

Common Belief: Scalar subqueries can return multiple columns as long as only one row is returned.

Reality: Scalar subqueries must return exactly one column; multiple columns cause syntax errors.

Why it matters: Trying to return multiple columns breaks the query and confuses beginners.

Expert Zone

1

Scalar subqueries that do not depend on outer queries can be optimized by MySQL to run once and cache results, improving performance.

2

Using LIMIT 1 in scalar subqueries can prevent errors but may hide data issues if multiple rows exist unexpectedly.

3

Correlated scalar subqueries can cause performance bottlenecks; rewriting them as JOINs or using window functions can be more efficient.

When NOT to use

Avoid scalar subqueries when they run per row on large datasets causing slow performance. Instead, use JOINs, derived tables, or window functions for better efficiency and clarity.

Production Patterns

In production, scalar subqueries are often used for quick lookups like fetching a single related value (e.g., max price, count) inside reports or filters. Experts carefully monitor their execution plans and rewrite them when they cause slowdowns.

Connections

Correlated subqueries

Scalar subqueries can be correlated, meaning they depend on outer query rows to produce values.

Understanding scalar subqueries helps grasp correlated subqueries, which are powerful but can impact performance if misused.

Joins

Joins and scalar subqueries can sometimes achieve the same results but differ in syntax and performance.

Knowing both approaches allows choosing the best tool for query clarity and speed.

Functional programming expressions

Scalar subqueries act like functions returning single values used inside expressions, similar to function calls in programming.

Recognizing this connection helps understand how databases treat subqueries as expressions, enabling complex computations within queries.

Common Pitfalls

#1 Scalar subquery returns multiple rows causing error.

Wrong approach:

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE location = 'NY');

Correct approach:

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE location = 'NY' LIMIT 1);

Root cause:

The subquery returns multiple department ids for location 'NY', but scalar subqueries must return only one value.

#2 Using scalar subquery that runs per row causing slow query.

Wrong approach:

SELECT name, (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) FROM employees e1;

Correct approach:

SELECT e1.name, d.max_salary FROM employees e1 JOIN (SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id) d ON e1.department_id = d.department_id;

Root cause:

Scalar subquery runs once per employee row, causing repeated work instead of a single grouped join.

#3 Scalar subquery returns no rows leading to unexpected NULL.

Wrong approach:

SELECT name FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 9999);

Correct approach:

SELECT name FROM employees WHERE salary > COALESCE((SELECT MAX(salary) FROM employees WHERE department_id = 9999), 0);

Root cause:

Subquery returns no rows for non-existent department, resulting in NULL that affects comparison.

Key Takeaways

Scalar subqueries return exactly one value and can be used wherever a single value is expected in SQL.

They allow embedding small queries inside larger ones, making SQL more powerful and flexible.

Understanding when scalar subqueries run and their dependencies is key to writing efficient queries.

Errors occur if scalar subqueries return multiple rows or unexpected NULLs; handling these cases is essential.

In production, scalar subqueries are useful but should be optimized or replaced with joins for large datasets.