Scalar subqueries in PostgreSQL - 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 value or number that you can use in your main query. This helps you get specific information from related data without writing complex joins. Scalar subqueries are often used in SELECT, WHERE, or HAVING clauses.

Why it matters

Scalar subqueries let you write simpler and clearer queries by embedding one query inside another. Without them, you would need to write longer, more complicated queries or multiple steps to get the same result. They save time and reduce errors when working with related data in databases.

Where it fits

Before learning scalar subqueries, you should understand basic SQL SELECT statements and simple WHERE conditions. After mastering scalar subqueries, you can learn about correlated subqueries, joins, and advanced query optimization techniques.

Mental Model

Core Idea

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

Think of it like...

Imagine you are baking a cake and need to know the exact number of eggs in the fridge. Instead of checking the whole fridge yourself, you ask a friend who quickly counts and tells you the number. That single number is your scalar subquery result, used right away in your recipe.

Main Query
  │
  ├─ Uses Scalar Subquery Result
  │      ┌───────────────┐
  │      │ Subquery:     │
  │      │ SELECT value  │
  │      │ FROM table    │
  │      │ WHERE ...     │
  │      └───────────────┘
  ↓
Final Result

Build-Up - 7 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 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 inner query finds the department id for 'Sales'.

Result

The main query uses the subquery result to filter employees in the Sales department.

Understanding that queries can be nested is the first step to using subqueries effectively.

2

Foundation

Scalar Subquery Returns One Value

Concept: Scalar subqueries must return exactly one value to be used in expressions.

A scalar subquery returns a single value, like a number or text. For example: SELECT name, (SELECT MAX(salary) FROM employees) AS max_salary FROM employees; The subquery returns the highest salary, used for every row.

Result

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

Knowing that scalar subqueries return one value helps avoid errors when using them in expressions.

3

Intermediate

Using Scalar Subqueries in WHERE Clause

🤔Before reading on: do you think a scalar subquery in WHERE can compare multiple rows or just one? Commit to your answer.

Concept: Scalar subqueries can be used in WHERE to compare a column to a single value from another table.

Example: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); This finds employees earning more than the average salary. The subquery calculates the average salary once.

Result

Only employees with salary above average are returned.

Using scalar subqueries in WHERE allows dynamic filtering based on aggregated or calculated values.

4

Intermediate

Scalar Subqueries in SELECT Clause

🤔Before reading on: do you think scalar subqueries in SELECT run once or for every row? Commit to your answer.

Concept: Scalar subqueries in SELECT can return a value for each row, often correlated with the outer query.

Example: SELECT name, (SELECT department_name FROM departments WHERE departments.id = employees.department_id) AS dept FROM employees; Here, the subquery finds the department name for each employee.

Result

Each employee's name is shown with their department name.

Scalar subqueries in SELECT let you add related data without joins, but they may run multiple times.

5

Intermediate

Handling NULL and Multiple Rows Errors

🤔Before reading on: what happens if a scalar subquery returns no rows or multiple rows? Commit to your answer.

Concept: Scalar subqueries must return exactly one row; otherwise, errors or NULL appear.

If a scalar subquery returns no rows, it results in NULL. If it returns more than one row, PostgreSQL throws an error: "more than one row returned by a subquery." To avoid this, use LIMIT 1 or aggregation functions.

Result

Queries either return a value, NULL, or error depending on subquery results.

Knowing how to handle subquery results prevents runtime errors and unexpected NULLs.

6

Advanced

Correlated Scalar Subqueries Explained

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

Concept: Correlated scalar subqueries depend on the outer query and run once per row.

Example: SELECT name, (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) AS max_dept_salary FROM employees e1; The subquery uses e1's department_id to find max salary per department.

Result

Each employee's name is shown with the max salary in their department.

Understanding correlation explains performance impact and how subqueries relate to outer rows.

7

Expert

Performance and Optimization of Scalar Subqueries

🤔Before reading on: do you think scalar subqueries always perform well or can cause slow queries? Commit to your answer.

Concept: Scalar subqueries can cause performance issues if run repeatedly; understanding query plans helps optimize them.

PostgreSQL may execute correlated scalar subqueries once per outer row, causing slowdowns. Using JOINs or WITH clauses can improve performance. EXPLAIN ANALYZE helps see query cost. Sometimes rewriting queries avoids repeated subquery execution.

Result

Optimized queries run faster and use fewer resources.

Knowing how scalar subqueries execute internally guides writing efficient queries and avoiding common performance traps.

Under the Hood

When PostgreSQL encounters a scalar subquery, it executes the inner query to produce a single value. For uncorrelated subqueries, this happens once. For correlated subqueries, the inner query runs once for each row of the outer query, substituting outer values. The database engine then uses this value in the outer query's expression or condition.

Why designed this way?

Scalar subqueries were designed to allow embedding complex logic inside queries without requiring explicit joins or multiple query steps. This design balances expressiveness and simplicity, letting users write concise queries. Alternatives like joins can be more complex or less intuitive for some use cases.

Outer Query
  │
  ├─ For each row (if correlated)
  │      ┌───────────────┐
  │      │ Scalar Subquery│
  │      │ Executes and   │
  │      │ returns one   │
  │      │ value         │
  │      └───────────────┘
  ↓
Use value in outer query expression

Myth Busters - 4 Common Misconceptions

Quick: Does a scalar subquery always return a value, or can it return multiple rows? Commit to your answer.

Common Belief: Scalar subqueries can return multiple rows just like normal subqueries.

Reality: Scalar subqueries must return exactly one value; returning multiple rows causes an error.

Why it matters: Assuming multiple rows are allowed leads to runtime errors and query failures.

Quick: Do scalar subqueries in SELECT run once or once per row? Commit to your answer.

Common Belief: Scalar subqueries in SELECT run only once per query, so they don't affect performance much.

Reality: Correlated scalar subqueries in SELECT run once per outer row, which can slow down queries significantly.

Why it matters: Ignoring this causes unexpected slow queries in production.

Quick: If a scalar subquery returns no rows, does it cause an error? Commit to your answer.

Common Belief: If a scalar subquery returns no rows, the query will fail with an error.

Reality: If no rows are returned, the scalar subquery evaluates to NULL, not an error.

Why it matters: Misunderstanding this can lead to incorrect NULL handling or unexpected results.

Quick: Can scalar subqueries replace all joins? Commit to your answer.

Common Belief: Scalar subqueries can always replace joins for related data retrieval.

Reality: Scalar subqueries are less efficient than joins for large datasets and cannot always replace them.

Why it matters: Using scalar subqueries instead of joins in large queries can cause performance problems.

Expert Zone

1

Scalar subqueries in SELECT clauses can be optimized by PostgreSQL using caching when uncorrelated, but correlated ones usually run repeatedly.

2

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

3

Scalar subqueries can be combined with window functions for advanced analytics, but this requires careful query planning.

When NOT to use

Avoid scalar subqueries when dealing with large datasets or when the subquery depends on many outer rows; use JOINs or CTEs (WITH clauses) instead for better performance and clarity.

Production Patterns

In production, scalar subqueries are often used for quick lookups of aggregated values or small reference data. Complex reports use them sparingly, favoring joins or materialized views. Monitoring query plans and execution times is standard practice to avoid performance bottlenecks.

Connections

Joins

Alternative approach

Understanding joins helps decide when to use scalar subqueries or joins for related data retrieval, balancing readability and performance.

Correlated Subqueries

Builds-on

Scalar subqueries are the foundation for correlated subqueries, which depend on outer query values and run per row.

Functional Programming

Similar pattern

Scalar subqueries resemble function calls returning single values inside expressions, showing how database queries can embed computations like programming functions.

Common Pitfalls

#1 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 IN (SELECT id FROM departments WHERE location = 'NY');

Root cause:

Using '=' expects one value, but subquery returns multiple rows; IN handles multiple values.

#2 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 = 999);

Correct approach:

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

Root cause:

No rows in subquery returns NULL, which can cause unexpected filtering; COALESCE provides a default.

#3 Using scalar subquery in SELECT without correlation causing repeated execution.

Wrong approach:

SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.employee_id = employees.id) FROM employees;

Correct approach:

SELECT employees.name, order_counts.count FROM employees JOIN (SELECT employee_id, COUNT(*) AS count FROM orders GROUP BY employee_id) order_counts ON employees.id = order_counts.employee_id;

Root cause:

Correlated scalar subquery runs once per row, causing performance issues; join with aggregation is more efficient.

Key Takeaways

Scalar subqueries return exactly one value and can be used inside expressions in SQL queries.

They simplify queries by embedding small queries inside larger ones, but must be used carefully to avoid errors and performance issues.

Correlated scalar subqueries run once per outer row and can slow down queries if not optimized.

Handling cases where subqueries return no rows or multiple rows is essential to avoid errors or unexpected NULLs.

Knowing when to use scalar subqueries versus joins or other SQL constructs is key for writing efficient and readable database queries.