Multi Row Subqueries in SQL
Introduction
A multi row subquery is a query nested inside another SQL statement that can return a set of rows. The surrounding statement does not expect one scalar answer. Instead, it asks whether a value belongs to the returned set, compares a value with some or all values in that set, or checks whether qualifying related rows exist.
SELECT employee_id, employee_name, department_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location = 'Chicago'
);
If the inner query returns department IDs 10 and 20, the outer query selects employees whose department is either 10 or 20. The database may physically optimize the statement as a semi-join or another plan, but the set-based interpretation remains the clearest way to reason about correctness.
What Is a Multi Row Subquery?
A multi row subquery is capable of returning more than one record to its outer SQL statement. It might return zero, one, or many rows for a particular execution. The classification describes its possible cardinality, not a promise that every execution produces at least two rows.
The outer expression must use an operator that understands a set. Common choices are IN, NOT IN, ANY, SOME, ALL, EXISTS, and NOT EXISTS. Ordinary scalar comparisons such as = (subquery) fail when the subquery supplies multiple rows.
Why Multi Row Subqueries Matter
Business questions often involve collections rather than one value. A company may need employees in all Chicago departments, products from approved suppliers, students registered for selected courses, customers with high-value orders, or accounts that have no failed compliance review. Each requirement first identifies a set and then applies that set to another operation.
Embedding the set-producing query avoids hardcoded identifiers and keeps the logic connected to current database data. If an additional supplier becomes approved or a new department opens in Chicago, the outer query naturally uses the updated set without changing SQL text.
Sample Tables
The examples use employees and departments. Assume employees 101 and 103 belong to Engineering department 10, employees 102 and 105 belong to Sales department 20, employee 104 belongs to Finance department 30, and employee 106 belongs to Support department 40. Engineering and Sales are in Chicago, Finance is in New York, and Support is in Dallas.
| Employee | Department | Salary |
|---|---|---|
| Alice | 10 | 60000 |
| Bob | 20 | 80000 |
| Carol | 10 | 50000 |
| David | 30 | 90000 |
| Emma | 20 | 70000 |
| Frank | 40 | 55000 |
Basic Syntax and Logical Flow
SELECT outer_columns
FROM outer_table
WHERE outer_expression set_operator (
SELECT inner_expression
FROM inner_table
WHERE inner_condition
);
Conceptually, SQL identifies the inner rows, projects the relevant values, and applies the outer operator. For the Chicago example, the inner result is 10 and 20. The outer condition then behaves like department_id IN (10, 20), returning Alice, Bob, Carol, and Emma.
This description is a logical model, not a required execution sequence. An optimizer can reorder operations, use indexes, transform membership into a join strategy, or stop after the first existence match. Correct SQL should express the intended relationship without relying on a particular physical order.
Operators for Multi Row Results
| Operator | Business meaning | Typical question |
|---|---|---|
IN | Matches at least one equal value | Is this ID in the selected set? |
NOT IN | Differs from every non-null value | Is this ID absent from the set? |
ANY / SOME | At least one comparison is true | Is salary above at least one result? |
ALL | Every comparison is true | Is salary above every result? |
EXISTS | At least one qualifying row exists | Does this customer have an order? |
NOT EXISTS | No qualifying row exists | Does this customer have no order? |
These operators are not interchangeable. Each expresses a different quantifier or relationship, and each has specific behavior for null values and empty result sets.
IN for Set Membership
IN checks whether the outer value equals at least one value returned by the subquery. It is usually the most readable operator when the requirement names a selected collection of identifiers, codes, categories, statuses, or other values.
SELECT product_id, product_name, supplier_id
FROM products
WHERE supplier_id IN (
SELECT supplier_id
FROM suppliers
WHERE status = 'APPROVED'
);
The query does not need to know which supplier IDs are approved in advance. The inner query derives the current approved set, and the outer query retrieves products associated with any member.
IN Versus Multiple OR Conditions
For a fixed list, department_id IN (10, 20, 30) expresses the same basic membership test as three equality predicates connected by OR. The IN form is shorter and communicates that the values form one conceptual set.
A subquery becomes especially useful when membership changes with data. Replacing a dynamic subquery with copied identifiers creates maintenance risk because application logic can become stale when rows are inserted, removed, or reclassified.
Duplicates in an IN Subquery
Duplicate inner values do not alter ordinary membership semantics. If an order query returns customer ID 42 ten times, customer_id IN (subquery) still asks whether 42 is present, not how many times it appears.
This does not mean duplicates are always harmless. They can signal a data-quality issue, increase intermediate work, and affect a rewrite that uses a normal join because a join can multiply outer rows. Avoid adding DISTINCT automatically; first decide whether duplicates are valid and inspect the plan.
Empty Results with IN
If the subquery returns no values, no outer value can belong to the set, so the membership condition is false for every row. An empty approved-supplier set therefore returns no products.
This behavior is often appropriate, but it should be tested. An empty inner set may represent a valid business state, missing reference data, an overly restrictive filter, or a deployment problem. SQL produces a consistent logical result even when the operational meaning requires investigation.
NOT IN and Set Exclusion
NOT IN appears to ask whether an outer value is absent from the returned set.
SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN (
SELECT department_id
FROM employees
);
If every inner department ID is non-null, this can find departments with no employee. The danger is that NOT IN is governed by SQL three-valued logic. A null in the subquery can make the result unknown rather than true.
The NULL Problem with NOT IN
Consider 30 NOT IN (10, 20, NULL). SQL can prove that 30 differs from 10 and 20, but it cannot determine whether 30 differs from the unknown value. The combined predicate becomes unknown, and a WHERE clause does not retain unknown rows.
This issue can cause a query expected to return unmatched rows to return none. Filtering inner nulls can be correct when null means "not applicable," but it should be deliberate:
WHERE department_id NOT IN (
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
);
NOT EXISTS as a Safer Anti-Relationship
When the business question is whether a related row is absent, NOT EXISTS usually states the requirement more directly and avoids the inner-null trap.
SELECT d.department_id, d.department_name
FROM departments AS d
WHERE NOT EXISTS (
SELECT 1
FROM employees AS e
WHERE e.department_id = d.department_id
);
The subquery is correlated to the current department. SQL asks whether it can find any matching employee. It does not build a negated list, so an unrelated employee row with a null department does not invalidate the result.
ANY for At-Least-One Comparison
ANY combines a comparison operator with a returned set. The predicate is true when at least one individual comparison is true. If the inner salaries are 50,000, 60,000, and 70,000, then 65,000 > ANY is true because 65,000 exceeds at least one value.
SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department_id = 10
);
This finds employees whose salary exceeds at least one salary in department 10. It does not mean greater than the average, and it does not mean greater than every department 10 salary.
SOME Is a Synonym for ANY
In standard quantified comparison, SOME and ANY express the same logic. salary > SOME (subquery) means that the salary is greater than at least one returned value.
ANY is more common in many codebases, but local conventions and database support should guide spelling. The important part is the quantifier: one true comparison is enough.
Understanding Greater Than ANY
For a nonempty set containing only non-null numeric values, x > ANY (set) behaves like asking whether x is greater than the minimum value. That shortcut can help intuition, but the quantified form remains important because null and empty-set behavior require three-valued logic.
Do not rewrite it mechanically to x > MIN(...) without considering that an aggregate over no rows returns null, while ANY over an empty set is false. Similar-looking forms can diverge at edge cases.
Understanding Less Than ANY
For a nonempty, non-null set, x < ANY (set) is true when x is less than at least one value, which resembles comparison with the maximum. A salary of 65,000 is less than any of 50,000, 60,000, and 70,000 because it is less than 70,000.
Natural-language phrasing can be misleading. "Less than any" sometimes sounds like less than every value in ordinary conversation, while SQL < ANY means at least one comparison. Translate the condition explicitly before writing it.
ALL for Every-Value Comparison
ALL requires every comparison to be true. With salaries 50,000, 60,000, and 70,000, a value must exceed 70,000 to satisfy > ALL.
SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department_id = 10
);
This query finds employees earning more than every employee in department 10. For a nonempty, non-null set, the condition resembles comparison with its maximum, but empty sets and nulls again deserve direct analysis.
Less Than ALL
x < ALL (set) means x is less than every returned value. For a nonempty, non-null numeric set, x must therefore be below its minimum. A value of 45,000 is less than all of 50,000, 60,000, and 70,000.
These quantified predicates are useful when the business requirement explicitly compares one value with an entire group: cheaper than every competing product, faster than every benchmark, or older than all unresolved records.
ANY Versus ALL
| Condition | Required truth | Nonempty non-null intuition |
|---|---|---|
x > ANY (set) | At least one | x is above the minimum |
x > ALL (set) | Every one | x is above the maximum |
x < ANY (set) | At least one | x is below the maximum |
x < ALL (set) | Every one | x is below the minimum |
The table is a reasoning aid, not permission to ignore nulls or empty sets. Quantified predicates should be tested with those cases because production data eventually supplies them.
Empty-Set Behavior of ANY and ALL
ANY over an empty result is false because there is no comparison that can be true. ALL over an empty result is true because no returned value violates the condition. This second result is known as vacuous truth.
For example, every employee satisfies salary > ALL of an empty department unless another condition prevents it. That can surprise developers who expect no comparison values to mean no match. Explicitly decide whether an empty comparison population is valid and add an existence requirement if the group must contain rows.
WHERE EXISTS (
SELECT 1 FROM employees WHERE department_id = 999
)
AND salary > ALL (
SELECT salary FROM employees WHERE department_id = 999
);
NULL Values with ANY and ALL
Each comparison with null produces unknown. For ANY, one true comparison still makes the overall result true; if no comparison is true and at least one is unknown, the result can be unknown. For ALL, one false comparison makes the result false; if none is false and at least one is unknown, the result can be unknown.
Filtering nulls may be appropriate when the domain says missing values should not participate. Otherwise, model the null meaning explicitly. A null salary might mean unavailable, not zero, and substituting zero changes business semantics.
Equality with ANY and IN
x = ANY (subquery) is generally equivalent to x IN (subquery). Both ask whether an equal value exists. x <> ALL (subquery) resembles x NOT IN (subquery) and shares important null concerns.
Prefer the form that communicates intent. IN is usually clearer for membership, while ANY and ALL are most valuable with ordered comparisons such as greater-than and less-than.
EXISTS for Related-Row Checks
EXISTS is true when its subquery returns at least one row. The selected expression is normally irrelevant, so SELECT 1 makes the intent clear.
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'COMPLETED'
);
The query returns each customer once even if the customer has hundreds of completed orders. An optimizer can stop searching after the first match, although the actual plan depends on the database and available indexes.
NOT EXISTS for Missing Relationships
NOT EXISTS is true only when no correlated row satisfies the inner predicates. It naturally expresses customers without orders, products without inventory, test cases without executions, or departments without employees.
Place all relationship and qualification predicates inside the subquery. If the requirement is customers with no completed orders, the status filter belongs inside the existence test. Moving it outside can change both meaning and row preservation.
Correlated and Non-Correlated Forms
A non-correlated subquery can run independently because it does not reference the outer row. The Chicago department set is non-correlated. A correlated subquery refers to the current outer alias, as in an EXISTS condition matching orders to the current customer.
Correlation is a logical dependency, not proof that the database executes the inner query row by row. Modern optimizers may decorrelate it into a join-like plan. Still, incomplete correlation predicates produce incorrect matches, especially in multi-tenant data.
Complete Composite Correlation
If a relationship uses multiple columns, include all of them. An order number may be unique only within a tenant, branch, or fiscal year. Matching on order number alone can connect unrelated rows.
WHERE EXISTS (
SELECT 1
FROM payments AS p
WHERE p.tenant_id = o.tenant_id
AND p.order_id = o.order_id
AND p.status = 'SETTLED'
);
Composite keys are correctness rules, not merely performance details. Indexes should usually align with the complete relationship and useful filters.
Single Row Versus Multi Row Subqueries
| Characteristic | Single row | Multi row |
|---|---|---|
| Possible rows | At most one | Zero, one, or many |
| Typical operators | =, >, < | IN, ANY, ALL, EXISTS |
| Question | Compare with one value | Compare with or test a set |
| Failure risk | Cardinality error | Incorrect quantifier or null logic |
Choose from the business requirement. Do not change equality to IN merely to suppress a cardinality error when the inner data was supposed to be unique.
Why Scalar Equality Fails
A condition such as department_id = (SELECT department_id ...) needs one value. If the inner query returns 10 and 20, SQL cannot choose which value should be used, so most databases raise a multiple-row error.
If both departments are valid, IN is correct. If only one department should exist, repair the query or data model. The error reveals a disagreement about expected cardinality and should not be hidden without understanding it.
IN Versus JOIN
A membership subquery and an inner join can sometimes return the same outer records, and optimizers may transform one into the other. Their visible semantics are not always identical. A join contributes columns from both sides and can produce one output row per match, while IN asks only whether a matching value exists.
If a customer has five qualifying orders, an ordinary join can return the customer five times unless results are grouped or deduplicated. IN or EXISTS naturally preserves one outer customer row. Use a join when inner detail belongs in the output; use a semi-join style predicate when only qualification matters.
EXISTS Versus IN
IN compares an outer value with a projected inner set. EXISTS checks whether a qualifying row exists, often using correlation. Both can express "customers who have orders," and an optimizer may choose similar plans.
Prefer IN when membership in a clearly defined value set is the natural description. Prefer EXISTS when the requirement is a relationship, especially a composite relationship. Avoid unsupported universal claims that one is always faster; inspect plans, indexes, cardinality estimates, and measurements.
Aggregates and HAVING in the Inner Query
A multi row subquery can group data and return keys for groups that satisfy an aggregate condition.
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5
);
The inner query returns every customer ID with at least five orders. Its grain is one row per qualifying customer, and the outer query retrieves customer details. A CTE or join may be clearer when the count must also be displayed.
Multi Row Subqueries in UPDATE
Data modification can use set membership, but it requires careful preview and transactional controls.
UPDATE products
SET review_required = 'Y'
WHERE supplier_id IN (
SELECT supplier_id
FROM suppliers
WHERE compliance_status = 'EXPIRED'
);
Run an equivalent SELECT first, confirm the inner set, check null behavior, and verify the expected number of affected rows. Database-specific rules may restrict reading from a table being modified, so test the exact platform syntax.
Multi Row Subqueries in DELETE
A delete can target rows associated with a returned set, such as expired sessions owned by disabled accounts. The risk is broader than with a read query because an incorrect set changes data.
Use explicit filters, backups or recovery controls, transactions where appropriate, and row-count validation. For anti-relationship deletion, prefer NOT EXISTS when nulls could appear in the projected key.
E-Commerce Scenario
An online store may need products belonging to categories that currently have an active campaign. A subquery can select active campaign category IDs, while the outer query retrieves product details. Another query can use EXISTS to find customers who placed at least one paid order during the campaign.
The two forms answer different questions. Category membership uses a value set; customer participation uses a related-row existence check. Keeping that distinction makes SQL easier to review and avoids accidental row multiplication.
Banking Scenario
A bank might identify accounts with balances greater than every account in a comparison portfolio using > ALL. It might find customers with at least one high-risk transaction using correlated EXISTS, or branches represented in an audit set using IN.
Financial queries demand explicit handling of currency, effective date, account status, and null values. A syntactically correct multi row comparison can still be wrong if it mixes currencies or includes closed accounts in the comparison population.
Inventory Scenario
Inventory systems often find products stocked in selected warehouses, products supplied by approved vendors, or products missing a reorder rule. IN works well for selected warehouses; EXISTS works for any stock record below threshold; NOT EXISTS works for missing configuration.
Where stock uses a composite key such as company, warehouse, and product, include the full key in correlation. Otherwise, stock in one company can incorrectly satisfy another company's query.
Education Scenario
A learning platform may select students enrolled in active courses, learners who completed any advanced module, or learners whose score exceeds all scores in a reference group. These map naturally to IN, EXISTS, and > ALL.
Enrollment tables frequently contain multiple terms and attempts. Include term, course version, and completion status when the requirement depends on them. A broad subquery may return technically related but historically irrelevant rows.
Software Testing Scenario
A QA database can retrieve test cases belonging to modules changed in a release, builds with at least one failed critical test, or requirements with no linked test case. Those questions map to IN, EXISTS, and NOT EXISTS.
SELECT r.requirement_id, r.title
FROM requirements AS r
WHERE NOT EXISTS (
SELECT 1
FROM test_case_requirements AS tcr
WHERE tcr.requirement_id = r.requirement_id
);
This anti-relationship query supports traceability analysis without being disrupted by null foreign keys elsewhere in the mapping table.
API Monitoring Scenario
An API observability system can find endpoints that produced any server error, services whose latency exceeds every agreed benchmark, or endpoints with no successful request during a period. The correct operator depends on whether the question is membership, quantified comparison, or existence.
Always constrain time windows and environments. A production endpoint should not be classified from test-environment events, and a historical failure should not necessarily affect the current reporting period.
Nested Multi Row Subqueries
A multi row subquery can itself contain joins or additional subqueries. For example, an outer product query can select category IDs returned by campaigns whose owners belong to approved organizations. Such nesting is valid but can become difficult to read.
Use meaningful aliases, format each logical level, and consider CTEs to name intermediate sets. The goal is not minimum line count; it is a query whose grains, relationships, and filters can be verified.
Using CTEs for Named Sets
WITH chicago_departments AS (
SELECT department_id
FROM departments
WHERE location = 'Chicago'
)
SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM chicago_departments
);
A CTE can make an important set easier to understand and reuse. It does not automatically materialize data or improve performance; behavior depends on the database and query. Use it primarily to clarify intent, then inspect the plan.
Indexes for Membership and Existence
Useful indexes normally support inner filters and relationship keys. The Chicago example can benefit from an index beginning with department location and including department ID, while the customer-order existence query can benefit from an index beginning with customer ID and possibly status.
Index order should reflect selective predicates and access patterns. More indexes are not always better because they consume storage and slow writes. Validate with realistic row counts, data distribution, and the actual execution plan.
Semi-Joins and Anti-Joins
Databases often implement IN or EXISTS as a semi-join: return an outer row when a match exists, without returning one copy per inner match. NOT EXISTS commonly maps to an anti-join: return an outer row when no match exists.
Understanding these concepts explains why existence predicates preserve outer-row grain. You usually do not write the words semi-join or anti-join in SQL, but they are useful when reading execution plans.
Performance Considerations
Performance depends on database version, indexes, statistics, cardinality, selectivity, data distribution, correlation, and available transformations. A large literal list, an unindexed correlated predicate, and a selective indexed existence test behave differently.
Start with correct, readable SQL. Then inspect estimated and actual plans, check row estimates, measure logical reads and elapsed time, and test production-like volumes. Rewriting a subquery as a join is not automatically an optimization; it may change output grain or produce the same physical plan.
Common Mistakes
- Using scalar equality when multiple inner rows are valid.
- Choosing
ANYwhen the requirement says every value. - Reading
< ANYas less than all values. - Using
NOT INwithout checking inner nulls. - Ignoring empty-set behavior for
ANYandALL. - Replacing a subquery with a join and accidentally multiplying rows.
- Omitting tenant, version, or date columns from correlation.
- Adding
DISTINCTwithout investigating duplicates. - Assuming textual execution order or universal performance rules.
- Running an update or delete before previewing its target set.
Debugging Workflow
- Run the inner query independently and inspect its rows.
- Confirm expected cardinality, columns, data types, and grain.
- Check duplicate and null values explicitly.
- Test the empty-set case.
- Translate
ANYorALLinto individual comparisons. - Verify every correlated key and filter.
- Confirm the expected grain of the outer result.
- Compare a join rewrite only if it preserves semantics.
- Review supporting indexes and the actual execution plan.
- Test boundary values and realistic data volume.
This workflow separates correctness from tuning. A fast query that answers the wrong set question is still defective.
Operator Selection Guide
| Requirement | Starting operator |
|---|---|
| Employee belongs to a selected department | IN |
| Customer has at least one order | EXISTS |
| Customer has no order | NOT EXISTS |
| Salary exceeds at least one comparison salary | > ANY |
| Salary exceeds every comparison salary | > ALL |
| Value is absent from a guaranteed non-null set | NOT IN |
| Composite relationship has a match | Correlated EXISTS |
Final Comparison Example
Suppose the subquery returns 50,000, 60,000, and 70,000, with no nulls. Comparing 65,000 produces the following results.
| Condition | Result | Reason |
|---|---|---|
65000 IN (...) | False | No exact value |
65000 NOT IN (...) | True | Different from each value |
65000 > ANY (...) | True | Above 50000 and 60000 |
65000 > ALL (...) | False | Not above 70000 |
65000 < ANY (...) | True | Below 70000 |
65000 < ALL (...) | False | Not below 50000 and 60000 |
Interview Questions and Answers
What is a multi row subquery?
It is a nested query capable of returning multiple rows for use by an outer SQL statement through set or existence operators.
Which operators support multi row results?
Common operators are IN, NOT IN, ANY, SOME, ALL, EXISTS, and NOT EXISTS.
What is the difference between ANY and ALL?
ANY needs at least one true comparison. ALL needs every comparison to be true.
Why is NOT IN dangerous with NULL?
A null inner value creates an unknown comparison, which can make the entire negated membership condition unknown and filter every row.
What happens with an empty subquery?
IN and ANY do not find a match and are false. ALL is true because no returned value violates the comparison.
Can a multi row subquery be correlated?
Yes. An EXISTS, NOT EXISTS, ANY, or other subquery can reference columns from the current outer row.
Is EXISTS always faster than IN?
No. Optimizers may transform both into similar plans. Performance depends on the database, statistics, indexes, predicates, cardinality, and data distribution.
Testing Strategy
Test zero, one, and many inner rows; duplicate values; inner and outer nulls; values at and around comparison boundaries; and correlated rows with no, one, and many matches. Test an empty set separately for ANY and ALL.
For modification statements, verify the selected keys before changing data and assert affected-row counts. For multi-tenant or versioned data, include cases with identical local IDs in different scopes to expose incomplete correlation.
Core Mental Model
IN
Does this value belong to the returned set?
NOT IN
Is this value different from every returned value?
Watch for NULL.
ANY / SOME
Is at least one comparison TRUE?
ALL
Is every comparison TRUE?
EXISTS
Does at least one qualifying row exist?
NOT EXISTS
Are there no qualifying rows?
Key Takeaway
Multi row subqueries let an outer SQL statement work with a dynamically derived set. Use IN for membership, ANY or SOME when one successful comparison is enough, ALL when every comparison must succeed, and EXISTS or NOT EXISTS for relationship presence or absence.
Correctness depends on more than choosing syntax that accepts several rows. Understand null and empty-set behavior, preserve complete relationship keys, protect outer-row grain, and select the operator that matches the business quantifier. After correctness is established, use realistic data and execution plans to evaluate performance.