Create Subquery Statements Using IN Keyword

When you're building a query around a subquery, build it in pieces rather than all at once. Get the inner query running on its own first, against the actual table, until it returns exactly the rows you expect. Only then wrap the outer SELECT around it. This makes the whole thing far easier to troubleshoot, since you'll know for certain what values the subquery is feeding into the outer query rather than guessing at both layers simultaneously.

This matters more than it sounds like it should. When a query built around a subquery returns the wrong rows, or none at all, it's easy to waste time second-guessing the outer query's logic when the actual problem is that the inner query never returned what you assumed it would. Running the subquery on its own first, and actually looking at its output before you build anything around it, eliminates that entire category of confusion before it starts.

Subqueries let you do a few things a single flat query can't: filter against a result set computed on the fly rather than typed out by hand, narrow your results creatively based on data pulled from another table entirely, or correlate two otherwise unrelated queries together in a single call to the database instead of running two queries and combining the results in application code. This lesson focuses specifically on the IN keyword, one of the two ways to compare a value against a subquery's output, covered alongside its counterpart, the equal qualifier, in the previous lesson.

A subquery is a table expression enclosed in parentheses. One restriction is easy to trip over: that expression can't be a bare JOIN. This is not a legal subquery:

( A NATURAL JOIN B )

But wrapping it in a SELECT makes it legal:

SELECT * FROM A NATURAL JOIN B

Subqueries come in three shapes, and the shape determines where you're allowed to use one. A table subquery can return any number of rows and columns; this is what IN is built for. A scalar subquery must return exactly one row and one column; this is what = requires, as covered in the previous lesson. A row subquery sits in between, returning exactly one row but potentially several columns, used in a handful of specialized comparisons this course doesn't need yet. The practical takeaway: if you're not certain your subquery will return exactly one value, use IN rather than =, and let the engine handle however many rows come back.

In the previous lesson, the subquery example assumed the Publishers table had exactly one row matching state = 'CA'. If there's more than one, the outer query still works, because IN compares against every value the subquery returns. It's worth seeing what that looks like with the values written out directly, before the subquery is even involved:

SELECT Title FROM Titles
WHERE pub_id IN ('1389','0736','0877');

Any row in Titles whose pub_id matches any value in that list is included in the results. This is a good way to use one table as a controlling key against another, here, Publishers effectively controls which rows come back from Titles. One detail worth remembering: the list always needs to be enclosed in parentheses, whether it's a literal list like this one or an actual subquery, since that's how the engine knows where the list starts and ends.

Writing the list out as literals only makes sense when the values are genuinely fixed and known ahead of time, three specific publisher IDs you happen to already know, for instance. The moment those values are themselves the result of some other condition in the database, rather than something you'd type from memory, that's the signal to replace the literal list with an actual subquery, which is exactly what the rest of this lesson builds toward.

IN doesn't require a subquery at all; it works just as well against a plain list of literal values. This query finds every member born in one of three specific years:

SELECT FirstName, LastName, YEAR(DateOfBirth)
FROM MemberDetails
WHERE YEAR(DateOfBirth) IN (1642, 1716, 1777);

FirstName	LastName	YEAR(DateOfBirth)
Isaac	Newton	1642
Gottfried	Wilhelm	1716
Karl	Gauss	1777

Replace the literal list with a SELECT statement, and IN works exactly the same way, except now the list of values is computed rather than typed out by hand. Suppose you want every member born in the same year that some film in the Films table was released:

SELECT FirstName, LastName, YEAR(DateOfBirth)
FROM MemberDetails
WHERE YEAR(DateOfBirth)
IN (SELECT YearReleased FROM Films);

FirstName   LastName   YEAR(DateOfBirth)
Katie       Smith      1977
Steve       Gee        1967
Doris       Night      1997

The subquery, (SELECT YearReleased FROM Films), returns a list of release years. Any member whose birth year appears anywhere in that list is included in the outer results.

IN isn't limited to comparing a single column against a single-column subquery. Oracle also supports matching a pair, or more, of columns at once against a subquery that returns the same number of columns, using a row constructor on the left side of IN:

SELECT employee_id, department_id, job_id
FROM employees
WHERE (department_id, job_id) IN (
    SELECT department_id, job_id
    FROM job_history
    WHERE end_date > DATE '2025-01-01'
);

This returns every employee whose current department_id and job_id combination matches some row in job_history ending after the given date. A single-column IN couldn't express this; matching on department_id alone, or job_id alone, would return a different, looser set of rows than matching on the exact pair together. This multi-column form is the same IN keyword, just compared against more than one value per row.

The same result can usually be produced with a join instead of a subquery:

SELECT FirstName, LastName, YEAR(DateOfBirth)
FROM MemberDetails JOIN Films ON YEAR(DateOfBirth) = YearReleased
GROUP BY FirstName, LastName, YEAR(DateOfBirth);

This returns the same rows as the subquery version above. Which one is actually faster depends on the specific database engine, the size of the tables involved, the available indexes, and the optimizer's own cost estimates; it isn't something you can predict reliably in the abstract, and it can change between engines or even between versions of the same engine. The practical rule is the one covered in the previous lesson: write the version you find clearest, and only spend time optimizing if testing on realistic data volumes actually shows a problem.

EXISTS is worth mentioning alongside IN here too, since it solves a related but distinct problem. IN compares a value against a returned list; EXISTS only asks whether a subquery returns any row at all, and doesn't care what values those rows contain. When you don't actually need the subquery's values, only whether a match exists, EXISTS is often the more efficient choice, since the engine can stop as soon as it finds one matching row rather than building out the full list IN would need:

SELECT FirstName, LastName, YEAR(DateOfBirth)
FROM MemberDetails m
WHERE EXISTS (
    SELECT 1
    FROM Films f
    WHERE f.YearReleased = YEAR(m.DateOfBirth)
);

This produces the same result set as the IN version above, but it's written as a correlated subquery rather than a self-contained one, since it references m.DateOfBirth from the outer query. Whether IN or EXISTS performs better for a given query is, again, something worth testing rather than assuming, but it's useful to have both tools available and to recognize when a query only cares about existence rather than the actual matched values.

There's one situation where a subquery is close to essential rather than just a matter of preference: finding rows that are not in a list, which is awkward to express as a join. Flip the earlier example around to find every member who was not born in the same year as any film's release:

SELECT FirstName, LastName, YEAR(DateOfBirth)
FROM MemberDetails
WHERE YEAR(DateOfBirth) NOT IN (SELECT YearReleased FROM Films);

One caution carried over from the previous lesson: if YearReleased in Films can ever contain a NULL, this NOT IN query can silently return zero rows for every member, regardless of their actual birth year, because comparing anything against a NULL produces an unknown result rather than a true or false one, and that unknown poisons the entire NOT IN list. If that column's nullability isn't guaranteed, rewrite the condition using NOT EXISTS instead, which doesn't share this failure mode.

Here's a case where a subquery isn't just convenient, it's the more maintainable choice. Suppose you want every employee who works in a sales-related department, and your DEPARTMENTS table has several: Sales, Government Sales, Retail Sales, and possibly more added later. You could hardcode those department names or their IDs directly into your WHERE clause, but then the query silently goes stale the moment a new sales department gets added or one gets renamed. Nobody gets an error when that happens; the query just quietly starts excluding employees it should be including, and that kind of bug can go unnoticed for a long time, since the query still runs and still returns plausible-looking results.

A subquery sidesteps that maintenance problem by computing the matching department IDs at the moment the query runs:

SELECT department_id
FROM departments
WHERE department_name LIKE '%Sales%';

Wrap that in a WHERE ... IN clause on the employees query, and the list of qualifying departments is always current:

SELECT last_name, first_name, hire_date, salary
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name LIKE '%Sales%'
)
ORDER BY last_name, first_name;

This is a non-correlated subquery, the inner query runs once, independent of the outer query, exactly the kind of subquery this lesson has focused on. Contrast that with a correlated version, where the inner query depends on the current outer row and has to re-run for each one, covered in depth in the previous lesson:

SELECT last_name, first_name, salary, department_id
FROM employees a
WHERE salary >
(SELECT AVG(salary)
 FROM employees b
 WHERE a.department_id = b.department_id);

This finds every employee earning more than their own department's average salary. It has to be correlated, since "their own department's average" is a different number for every row.

Every IN example so far has been non-correlated, the subquery runs once, independent of the outer query. But IN can be correlated too; it isn't limited to the standalone form. Suppose you want the single longest-tenured employee, by employee ID, in each department:

SELECT e.last_name, e.department_id
FROM employees e
WHERE e.employee_id IN (
    SELECT MIN(e2.employee_id)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

The inner query references e.department_id from the outer query's current row, which makes it correlated, re-evaluated once per department rather than computed a single time. For each outer row, the subquery finds the lowest employee_id within that same department, and IN matches the outer row against that single-value result. The fact that the subquery here always returns exactly one row doesn't change which keyword is appropriate; = would work just as well in this specific case, but IN handles it without any risk if a future change to the query's logic ever caused the subquery to return more than one value.

A handful of mistakes account for most of the trouble beginners run into with IN and subqueries.

Forgetting the parentheses. Whether the list is literal values or a subquery, IN requires it wrapped in parentheses. Leaving them off is a syntax error the engine will catch immediately, but it's an easy thing to drop when converting a hardcoded list into a subquery in a hurry.

Reaching for = instead of IN out of habit. If a subquery might ever return more than one row, and it isn't obviously guaranteed to be scalar, IN is the safer default. Using = and getting ORA-01427: single-row subquery returns more than one row at runtime, sometimes only once the underlying data grows, is one of the most common subquery errors, and it's covered in more depth in the previous lesson.

Mismatched column counts in a multi-column IN. When comparing (col1, col2) IN (subquery), the subquery's SELECT list has to return exactly as many columns, in the same order, as the row constructor on the left. A subquery returning three columns against a two-column row constructor fails immediately, not silently, but it's still a mismatch worth double-checking before running the query against a large table.

Assuming NOT IN behaves like IN with the logic flipped. As covered above, it doesn't, once NULL enters the picture. Treating NOT IN as a safe drop-in replacement for IN with an inverted condition is exactly the assumption that leads to the silent empty-result-set problem.

This lesson covered several distinct ways to reach for IN: against a literal list, against a subquery's computed list, against multiple columns at once, and correlated against the current outer row. Alongside it, EXISTS and NOT EXISTS came up as the tools to reach for when nullability or pure existence checking matters more than the matched values themselves. Every one of these shares the same underlying shape, checking whether a value belongs to some set, whether that set is typed by hand or computed by the database.

Click the link below to work on the Subquery Statement - Exercise.