SQL Subquery Statement Options

When you compare a value against the result of a subquery, you have two real options: an IN clause, or the equal (=) qualifier. They are not interchangeable, and picking the wrong one either produces a runtime error or silently returns the wrong rows. The choice comes down to one question: how many rows do you expect the subquery to return?

This matters more than it might first appear, because the wrong choice doesn't always fail loudly. A subquery that returns one row today might return several once the underlying data grows, and a query built around = will keep working right up until that happens, then fail in production with no warning. Understanding which option fits which situation, and why, is what this lesson covers.

Use IN when the subquery may return zero, one, or many rows. This is the standard way to compare a value against a set of results:

SELECT *
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);

IN is functionally equivalent to = ANY or = SOME. It doesn't care how many rows the subquery returns, which makes it the safer default when you're not certain the result will be a single value. Oracle accepts either keyword written out explicitly, and they behave identically:

SELECT *
FROM employees
WHERE department_id = ANY (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);

This produces exactly the same result as the IN version above. In practice, almost everyone writes IN rather than = ANY, since it reads more naturally, but it's worth recognizing = ANY when you encounter it in someone else's code, since it's not a different operation, just a different spelling of the same one.

Use = only when the subquery is guaranteed to return a single row and column, what's called a scalar subquery:

SELECT *
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

If that subquery ever returns more than one row, = fails at runtime with ORA-01427: single-row subquery returns more than one row. IN would have handled the same situation without complaint. If the subquery returns no rows at all, the comparison evaluates to unknown (NULL), and the outer row simply isn't selected, no error, just no match.

Subquery result	IN	=
0 rows	Works	Comparison is unknown; no match
1 row	Works	Works, and often clearer
Many rows	Works	Runtime error (ORA-01427)

One trap worth knowing before it bites you: NOT IN and NULL don't mix well. If the subquery behind a NOT IN returns even one NULL, the entire comparison can silently return zero rows, not an error, just an empty result set that looks like a bug in your data rather than a bug in the query. Here's why: NOT IN is logically evaluated as a chain of ANDed inequality comparisons, and the moment any one of those comparisons involves a NULL, the whole chain evaluates to unknown rather than true, which excludes the row. Consider:

SELECT title
FROM titles
WHERE pub_id NOT IN (
    SELECT pub_id
    FROM publishers
    WHERE pub_id IS NULL OR state = 'CA'
);

If even one row in publishers has a NULL pub_id, this query returns nothing at all, silently, for every title, regardless of what pub_id values actually exist. NOT EXISTS sidesteps the problem entirely, because it's checking for the existence of a matching row rather than comparing values directly against a list that might contain an unknown:

SELECT title
FROM titles t
WHERE NOT EXISTS (
    SELECT 1
    FROM publishers p
    WHERE p.pub_id = t.pub_id
      AND p.state = 'CA'
);

This version behaves correctly regardless of whether any pub_id in publishers is NULL, which is why NOT EXISTS is generally the safer default over NOT IN whenever nullability of the subquery's column isn't guaranteed.

As a rule of thumb: if the question is "does this value match any of these," reach for IN. If the question is "does this value match this one specific computed number," = against a scalar subquery, typically wrapped in MAX, MIN, COUNT, or a filter that can only ever match one row, is the clearer choice.

Subqueries evaluate from the inside out. The database runs the innermost query first, then substitutes its result into the outer query as if you'd typed the literal value yourself. Examine the series of steps below to see the evaluation process for a subquery statement, starting with a query that finds every book title from a publisher based in California.

SELECT Title FROM Titles WHERE
pub_id IN (SELECT Pub_ID FROM
Publishers WHERE State='CA')

(SELECT Pub_ID FROM
Publishers WHERE State='CA')

SELECT Title FROM Titles
WHERE pub_id IN ('1389')

This inside-out evaluation is the same principle as solving a math expression with nested parentheses: you resolve the innermost parentheses first, then work outward, using each result to complete the next level up. That parallel is worth sitting with for a moment, since it's the mental model that makes every other example in this lesson easier to trace: whatever's in the innermost set of parentheses always finishes first, no matter how many layers surround it.

Every example so far has been a non-correlated subquery: the inner query can run completely on its own, with no dependency on the outer query, and its result gets computed once. A correlated subquery is different. It references a column from the outer query, which means it has to be re-evaluated once per row of the outer query rather than just once overall.

Here's a correlated subquery that, for each person, calculates the average weight of everyone else who shares that person's last name:

SELECT id, firstname, lastname, weight,
    (SELECT AVG(weight)
     FROM person sq
     WHERE sq.lastname = p.lastname
    ) AS family_average
FROM person p
ORDER BY lastname, weight;

The inner query references p.lastname, a column from the outer query's current row, aliased as p. That reference is what makes it correlated: the subquery can't be evaluated once and reused, because its result depends on which outer row is currently being processed. For a table of a thousand people, this subquery potentially runs a thousand times, once per row, rather than once total. Scale that table up to a million rows and the difference between "once" and "once per row" stops being a rounding error and starts being the difference between a query that returns instantly and one that times out.

That repeated execution is exactly why correlated subqueries tend to cost more than non-correlated ones. Many correlated subqueries have an equivalent JOIN or GROUP BY formulation that performs better, and the query optimizer will sometimes rewrite one into the other internally, though it can't always find an equivalent form. The distinction between correlated and non-correlated isn't specific to this one example; it applies across every kind of subquery you'll encounter.

It's worth connecting this back to the NOT EXISTS pattern from earlier, since EXISTS and NOT EXISTS are themselves almost always written as correlated subqueries. The NOT EXISTS example above referenced t.pub_id from the outer query inside the inner one, which makes it correlated by definition, re-evaluated once per row of titles rather than computed a single time. That's a reasonable cost to accept in exchange for correct NULL handling, but it's worth knowing you're making that trade, not getting both correctness and a single-pass execution for free.

A subquery can itself contain another subquery. Nothing about the inside-out evaluation changes; there are just more layers to resolve before you reach the outermost query:

SELECT MemberId FROM MemberDetails
WHERE MemberId = (SELECT MAX(FilmId) FROM Films);

The WHERE clause on the outer query requires MemberId to equal whatever single value the inner query returns. That inner query can, in turn, contain its own subquery:

SELECT MemberId FROM MemberDetails
WHERE MemberId = (SELECT MAX(FilmId) FROM Films
WHERE FilmId IN (SELECT LocationId FROM Location));

Here the innermost query runs first, filtering Films down to only the rows whose FilmId also appears in Location. Its result feeds the MAX(FilmId) calculation one level up, and that result feeds the outermost comparison. Each layer resolves before the one surrounding it, the same inside-out rule as before, just applied twice.

Nesting like this works at any depth the database allows, but readability suffers well before you hit any actual limit. A query with three or four levels of nested subqueries is genuinely hard for another person, or for you in six months, to trace by eye. When nesting gets deep enough to hurt readability, a WITH clause (a common table expression, or CTE) is often a better fit: it lets you name each intermediate result and read the query roughly top to bottom instead of inside out.

WITH valid_films AS (
    SELECT FilmId
    FROM Films
    WHERE FilmId IN (SELECT LocationId FROM Location)
),
latest_film AS (
    SELECT MAX(FilmId) AS FilmId
    FROM valid_films
)
SELECT MemberId
FROM MemberDetails
WHERE MemberId = (SELECT FilmId FROM latest_film);

This produces the same result as the double-nested version above, but each step has a name, and the logic reads in the order it actually executes rather than requiring you to find the innermost parentheses first. Whether a given query is clearer nested or clearer as a CTE is partly a judgment call, but as a general guideline, two levels of nesting is usually fine to read directly; three or more is a reasonable signal to reach for a CTE instead.

Subqueries aren't always the most efficient way to express a comparison. Consider a query that retrieves every employee along with their department name, but only for departments located in New York:

SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
WHERE e.DepartmentID IN (
    SELECT DepartmentID
    FROM Departments
    WHERE Location = 'New York'
);

The same result can be produced with a join instead:

SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.Location = 'New York';

The subquery version scans Departments first to build a list of matching DepartmentID values, then scans Employees and filters against that list, two separate operations. The join version combines both tables in a single pass, matching rows on DepartmentID directly, then filters the combined result to New York. That single-pass structure generally gives the query optimizer more room to use indexes effectively and avoid redundant scans.

The trade-offs, in short:

• Joins tend to perform better on plain filter-list comparisons like this one, because they let the optimizer use indexes on the join condition, often complete the work in a single pass, and give the optimizer more join-algorithm options to choose from based on table size and available indexes.
• Subqueries remain the clearer choice once the logic gets more complex than a simple filter list, especially for correlated conditions with no clean join equivalent, or when you specifically want a computed scalar value (a MAX, a COUNT) rather than a set of rows to join against.

When a subquery is functioning purely as a filter list, the way IN is used above, it's worth checking whether rewriting it as a join produces a simpler execution plan. The actual performance difference depends on table size, available indexes, and the specific optimizer you're running against, so it's worth testing rather than assuming either form is automatically faster.

Pulling this lesson's threads together into a single decision path:

1. Do you need a set of rows to join against, or one computed value? A set means IN; a single guaranteed value means =.
2. Does the subquery reference a column from the outer query? If yes, it's correlated, and it'll run once per outer row rather than once overall; budget for that cost, or look for a JOIN/GROUP BY equivalent.
3. Is NULL a possibility in the subquery's column, combined with NOT IN? If so, use NOT EXISTS instead to avoid silently losing your entire result set.
4. Is the subquery just building a filter list from an otherwise unrelated table? Try the join equivalent and compare; it's frequently the more efficient shape for exactly that pattern.
5. Are you three or more levels deep in nested subqueries? Consider restructuring as a WITH clause so the query reads in execution order instead of inside out.

None of these five questions has a universally right answer independent of context, but each one narrows the decision down to something concrete rather than a vague sense that "subqueries are slow" or "joins are always better." Both tools solve real problems; the skill is recognizing which situation you're actually in.

For a deeper, hands-on treatment of writing and optimizing SQL queries like the ones in this lesson, SQL Data Analytics is a solid next step.