Course contentsShow
← Data Science
Lesson 960 of 2,14525. SQL Subqueries and CTEsFree lesson

Subqueries Returning Single Values

Use scalar subqueries that return one value for comparison operations like =, >, <, >=, <= in WHERE clauses.

Subqueries Returning Single Values

What you'll learn: How to use subqueries that return a single value for comparisons in WHERE clauses.

What is a Scalar Subquery?

A scalar subquery is a subquery that returns exactly one value—a single row with a single column. Think of it like asking a question that has one specific answer, then using that answer in a comparison.

You already know how to use subqueries in WHERE clauses. Now you'll learn to use them with comparison operators (=, >, <, >=, <=) when the subquery returns just one value.

How It Works

Imagine you want to find all products priced above the average. You can't just write WHERE price > AVG(price) because aggregate functions don't work directly in WHERE clauses. Instead, you use a subquery:

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

The subquery (SELECT AVG(price) FROM products) calculates one number—let's say 50.00—and the outer query becomes WHERE price > 50.00.

Common Patterns

Finding records above/below an aggregate:

WHERE salary > (SELECT AVG(salary) FROM employees)

Comparing to a specific record:

WHERE order_date > (SELECT hire_date FROM employees WHERE employee_id = 100)

Equality checks:

WHERE department_id = (SELECT department_id FROM employees WHERE name = 'John')

The key requirement: your subquery must return exactly one value. If it returns zero rows or multiple values, you'll get an error or unexpected behavior.

Key Takeaway: Scalar subqueries return a single value that you can use with comparison operators (=, >, <, >=, <=) in WHERE clauses, enabling dynamic filtering based on calculated or looked-up values.