Using EQUALS Clause with Subquery

The previous two lessons covered IN, which compares a value against a list a subquery returns. This lesson covers the other option: the equals sign, =, used when the subquery is guaranteed to return exactly one value rather than a list.

The two approaches aren't competing ways to do the same thing; they answer different questions. IN asks "does this value appear anywhere in this set?" = asks "does this value match this one specific computed number?" Using the wrong one doesn't always fail loudly, which is exactly why it's worth understanding precisely what each one requires rather than picking whichever one happens to work on your test data today.

Strictly speaking, SQL doesn't have a formally named "EQUALS clause" the way it has a WHERE clause or a GROUP BY clause. What's really happening is the ordinary equality operator, =, being used to compare a column against a subquery instead of against a literal value. The rule that makes this legal is simple to state but easy to get wrong in practice: the subquery on the right side of = must return exactly one row and exactly one column, a scalar subquery, as covered in Lesson 7.

What happens when that rule isn't met depends on which way it fails:

• Zero rows returned: the subquery's value is treated as NULL, and comparing anything to NULL with = produces an unknown result rather than a definite true or false. In a WHERE clause, a row with an unknown result is excluded, the same practical outcome as if the condition were false, but it's worth knowing the underlying reason is NULL comparison rather than an actual FALSE evaluation. That distinction matters the moment you wrap the condition in NOT or combine it with OR, where NULL's three-valued logic behaves differently than a plain FALSE would.
• More than one row returned: the comparison is ambiguous, since = has no way to decide which of several returned values to compare against. Oracle raises ORA-01427: single-row subquery returns more than one row at runtime.

The NOT-wrapping distinction is worth seeing rather than just stating. Suppose Departments has no row at all for 'Marketing':

SELECT Name
FROM Employees
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'Marketing'
);

This returns zero rows, as expected: the subquery evaluates to NULL, DepartmentID = NULL is unknown for every employee, and WHERE excludes every row with an unknown result. Now wrap the same condition in NOT:

SELECT Name
FROM Employees
WHERE NOT (DepartmentID = (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'Marketing'
));

If the inner comparison had genuinely evaluated to FALSE, negating it would produce TRUE for every row, meaning this query should return every employee. It doesn't. NOT applied to an unknown result is still unknown, not TRUE, so this query also returns zero rows, not the full employee list. That's the concrete difference between FALSE and UNKNOWN that the zero-row case actually produces, and it's exactly the kind of behavior that looks like a bug if you assumed the simpler, imprecise "evaluates to FALSE" description.

Consider two tables. The Employees table:

| EmployeeID | Name     | DepartmentID |
|------------|----------|--------------|
| 1          | John Doe | 2            |
| 2          | Jane Doe | 3            |

And the Departments table:

| DepartmentID | DepartmentName |
|--------------|-----------------|
| 1            | HR              |
| 2            | IT              |
| 3            | Finance         |

To find the name of the employee who works in the IT department, without knowing IT's DepartmentID ahead of time, a subquery on the right side of = looks it up at query time:

SELECT Name
FROM Employees
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'IT'
);

The inner query, SELECT DepartmentID FROM Departments WHERE DepartmentName = 'IT', returns exactly one value, 2, since department names are expected to be unique. The outer query then filters Employees down to rows where DepartmentID equals that one value, returning John Doe.

This is the same pattern covered in Lesson 6 with the Titles and Publishers tables, just with = instead of IN, since that earlier example also happened to return a single publisher:

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

This version only works because exactly one publisher is based in California. If a second California publisher were added to the table tomorrow, this exact query would start failing with ORA-01427 the next time it ran, with no change to the query itself, only to the data. That fragility is exactly why Lesson 6 recommended IN as the safer default whenever you're not completely certain the subquery will stay scalar.

It's worth being concrete about what that failure actually looks like, since "the query breaks eventually" is easy to dismiss until you've watched it happen. Suppose a second California-based publisher gets added:

| Pub_ID | State |
|--------|-------|
| 1389   | CA    |
| 9952   | CA    |

The subquery now returns two rows instead of one. Nothing about the outer query changed, and nothing in the schema prevented this from happening; it's simply new data. The very next time this query runs, it fails at execution time with ORA-01427, not at the moment the second publisher was inserted. That gap, between when the data changed and when the query actually breaks, is what makes this class of bug frustrating to track down: the person who added the new publisher may have no idea a reporting query somewhere depends on California having exactly one.

None of this means = is the wrong choice whenever a subquery is involved; it means = carries an assumption that has to actually hold. There are good reasons to reach for it deliberately rather than defaulting to IN everywhere:

• It documents intent. Writing = tells the next person reading the query that you expect exactly one value, which IN doesn't communicate on its own. If that assumption is later violated, you get an explicit error rather than a query that runs to completion and quietly returns the wrong thing.
• It's often paired with an aggregate function that mechanically guarantees scalarity. MAX, MIN, COUNT, SUM, and AVG always collapse their input to a single value regardless of how many rows they scanned, which removes the fragility seen in the publisher example above. WHERE salary = (SELECT MAX(salary) FROM employees) can never return more than one row from that subquery, no matter how the underlying data changes.
• It reads more directly. WHERE department_id = (subquery) more clearly signals "compare against this one computed thing" than WHERE department_id IN (subquery) does, even when both would produce identical results on today's data.

The practical guideline: reach for = when the subquery's scalar-ness is structurally guaranteed, typically because it's built around an aggregate function or a comparison against a column known to be unique, rather than because the current data merely happens to produce one row.

One useful way to think about a subquery: it behaves like a temporary table that exists only for the duration of the statement it's part of. The server computes its result, the outer query uses that result, and then the data is discarded once the statement finishes, no cleanup required and nothing left behind. This is a genuinely different kind of object from an actual temporary table you might create explicitly and query across multiple statements; a subquery's result has no name, can't be referenced a second time, and never outlives the single statement that created it. That statement-scoped lifetime is part of what makes subqueries lightweight to use freely, there's no setup or teardown cost the way there would be with a real temporary table.

A single aggregate function is a common way to guarantee a subquery stays scalar, since functions like MAX, MIN, COUNT, and SUM always collapse their input down to one value:

SELECT MAX(account_id) FROM account;

MAX(account_id)
----------------
29

Substituting that subquery into a WHERE clause produces a single combined query:

SELECT account_id, product_cd, cust_id, avail_balance
FROM account
WHERE account_id = (SELECT MAX(account_id) FROM account);

Because the subquery is guaranteed to return exactly one row, this is equivalent to substituting the literal value directly, the same inside-out substitution principle from Lesson 6:

SELECT account_id, product_cd, cust_id, avail_balance
FROM account
WHERE account_id = 29;

account_id  product_cd  cust_id  avail_balance
----------  ----------  -------  --------------
29          SBL         13       50000.00

If you're ever unsure what a subquery is actually returning, running it on its own, exactly as shown above with MAX(account_id), is the fastest way to check before you build the outer query around it, the same troubleshooting habit covered in the previous lesson.

MAX is just one aggregate function that pairs naturally with =. The same pattern works with any of them, and each answers a slightly different question:

SELECT last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

This finds whoever earns the least, the mirror image of the MAX example. COUNT and AVG work the same way, though the comparison usually stops being pure equality once an aggregate like AVG is involved, since matching a salary to the exact average is rare; more often you'd use > or < against an AVG subquery instead, which is exactly the correlated pattern covered in Lesson 6 comparing each employee's salary to their own department's average:

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

The single-value rule that applies to = applies identically to >, <, >=, and <= when they're used against a subquery; every comparison operator that isn't IN expects a scalar subquery on the other side, for the same reason = does.

The subquery on the right side of = isn't limited to a bare SELECT column FROM table. It can include WHERE, JOIN, GROUP BY, HAVING, or any other clause a standalone SELECT statement supports, as long as the whole thing still resolves down to exactly one row and one column by the time it finishes. This is what makes scalar subqueries genuinely useful rather than a narrow special case:

SELECT last_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(e.salary)
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name = 'Finance'
);

This subquery joins two tables and filters with WHERE before the MAX aggregate collapses everything down to a single value, the highest salary within the Finance department specifically. From the outer query's perspective, none of that internal complexity matters; it just sees one number to compare against, exactly the same as the simpler MAX(salary) example earlier. The rule that constrains a scalar subquery is about its final output shape, one row and one column, not about how simple or complex the query producing that output is allowed to be.

One clause that doesn't make sense here is ORDER BY on its own within a scalar subquery, since ordering only matters when multiple rows are being returned for someone to read in sequence; a subquery that's guaranteed to collapse to a single value has nothing left to order by the time = gets involved.

A Word of Caution About Forcing Scalarity

It's tempting, when a subquery occasionally returns more than one row and you just want the error to go away, to force it down to one row artificially, wrapping it with something like ROWNUM = 1 or FETCH FIRST 1 ROW ONLY rather than fixing the underlying assumption. Resist that instinct. Forcing scalarity this way doesn't fix the ambiguity the extra rows represented; it just picks one of the ambiguous rows silently, typically whichever one the database happens to return first, which isn't guaranteed to be consistent or meaningful. If the publisher example above had two California publishers and you forced the subquery to ROWNUM = 1, the query would stop raising ORA-01427, but it would now silently pick an arbitrary one of the two publishers rather than surfacing the fact that "the" California publisher isn't actually unique anymore. An error you have to fix is almost always better than a query that runs cleanly while quietly guessing.

Looking Ahead

This lesson covered several things worth carrying forward:

• = requires a scalar subquery, exactly one row and one column, while IN accepts any number of rows.
• A subquery returning zero rows makes the comparison evaluate to unknown, not FALSE, which only becomes visible once you wrap the condition in NOT or combine it with other logic.
• A subquery returning more than one row raises ORA-01427 at runtime, often long after the query was originally written, once the underlying data changes.
• Aggregate functions are the most reliable way to guarantee a subquery stays scalar structurally, rather than merely by coincidence of today's data.
• A scalar subquery can contain any clause a standalone query can, joins, filters, and grouping among them, as long as the final result collapses to one value.

The recurring theme across both this lesson and the choice between IN and = covered previously is the same: know what your subquery is structurally guaranteed to return, not just what it happens to return against today's data, and let that guarantee, not the current row count, decide which comparison operator belongs in your query.

The next lesson covers the DISTINCT keyword and how it fits into SELECT statements, including subqueries.