Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 38 ยท Joins, Subqueries and Hierarchies

Subqueries and Set Operators

Nest queries with scalar, IN, EXISTS and correlated subqueries, use inline views and the WITH clause, and combine results with UNION, INTERSECT and MINUS.

A subquery is a SELECT inside another statement. It lets you answer a question in two steps without leaving SQL: "find the average salary, then list everyone paid above it". A set operator works the other way around, stacking the results of two complete queries into one.

Everything in this lesson runs in the editor except Oracle's MINUS, which has a stand-in (EXCEPT). The syntax is otherwise identical in Oracle.

Scalar subqueries

A subquery that returns exactly one row and one column can be used anywhere a single value fits: in WHERE, in the SELECT list or even in ORDER BY.

SQL Above the average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

If a scalar subquery returns more than one row, Oracle raises ORA-01427: single-row subquery returns more than one row. If it returns no rows, the value is NULL.

IN, ANY and ALL

When a subquery returns a list, use IN, or compare with ANY / ALL:

SQL IN with a subquery ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

x > ALL (subquery) means greater than every value; x > ANY (subquery) means greater than at least one. = ANY is the same as IN. Avoid NOT IN with a subquery that can return NULL: one NULL makes the whole condition unknown and returns no rows. Use NOT EXISTS instead.

Correlated subqueries and EXISTS

A correlated subquery refers to a column of the outer query, so it is re-evaluated for each outer row. EXISTS tests whether the subquery returns at least one row and stops at the first:

SQL EXISTS and NOT EXISTS ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

What you select inside EXISTS is irrelevant (SELECT 1 is the convention). Correlated subqueries also express "above the average of their own group":

SQL Correlated comparison ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Oracle's optimizer often rewrites EXISTS and IN into the same semi-join plan, so choose the one that reads best. NOT EXISTS is safer than NOT IN.

Inline views

A subquery in the FROM clause is an inline view: a temporary table that exists for one statement. Oracle needs no alias for it, although a column alias inside is required for expressions:

SQL Inline view ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The WITH clause (CTEs)

Long queries read better when the pieces have names. The WITH clause, called a common table expression, defines them up front:

SQL WITH clause ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

In Oracle, each named query may use the ones before it, and the optimizer may either inline it or materialize it as a temporary result; the hint /*+ MATERIALIZE */ forces the second. Chapter 9 returns to hints. The WITH clause is also the home of recursive queries (see hierarchical queries).

Set operators

A set operator joins the results of two queries vertically. Both queries must return the same number of columns with compatible types; the column names come from the first query.

Operator Result
UNION ALL All rows from both, duplicates kept (fast)
UNION All rows, duplicates removed (sorts or hashes)
INTERSECT Rows that appear in both
MINUS Rows of the first query that are not in the second
SQL UNION and UNION ALL ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Prefer UNION ALL whenever duplicates cannot occur or are fine: UNION pays for removing them. An ORDER BY goes once, at the very end, and must use the first query's column names or positions.

INTERSECT and MINUS compare whole rows and treat two NULLs as equal. Oracle spells the difference operator MINUS. From Oracle 21c, the standard EXCEPT is accepted as a synonym. The stand-in uses EXCEPT:

SQL Stand-in for MINUS ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL INTERSECT ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Only rows that appear in both result sets are returned. The empty first row is the department-less CEO: INTERSECT treats two NULLs as equal.

Choosing a subquery form

Which subquery form?

Scalar One value, used like a constant
IN / ANY / ALL A list to compare with
Correlated Re-evaluated per outer row
Inline view / WITH A named temporary result

Prefer NOT EXISTS over NOT IN when NULLs are possible.

1 / 2

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A scalar subquery returns two rows. What does Oracle do?

    A scalar subquery must return at most one row. Use IN, aggregates or FETCH FIRST 1 ROW ONLY if needed.
  2. 2. Why is NOT EXISTS safer than NOT IN (subquery)?

    A NULL in a NOT IN list makes the comparison unknown for every row. NOT EXISTS only checks whether matching rows exist.
  3. 3. Which set operator keeps duplicate rows?

    UNION ALL appends the results without removing duplicates, so it is also the cheapest.
  4. 4. Where do the column names of a UNION result come from?

    The first SELECT defines the names. ORDER BY must refer to them or to column positions.

Summary

  • Scalar subqueries return one value, IN/ANY/ALL compare with a list, EXISTS tests for rows.
  • Correlated subqueries refer to the outer row; prefer NOT EXISTS to NOT IN.
  • Inline views and the WITH clause give intermediate results names and make queries readable.
  • UNION ALL, UNION, INTERSECT and MINUS stack results; they need matching column counts and types.

Next: Hierarchical Queries with CONNECT BY