Understanding Subqueries and Their Types

A subquery is – to put it simply – a query within a query.

What purpose does a subquery serve?

A subquery may be needed when it takes more than a single step to reach the answer.

Suppose we need to find all employees who work in the same department as KING. We need to:

1. Find which department KING works for, say D
2. List all employees who work in D

More than one step? Subquery at your service!

SQL> select oe.ename from emp oe
  2  where oe.deptno =
  3      -- Subquery to get D starts
  4         (select ie.deptno
  5          from emp ie
  6          where ie.ename = 'KING')
  7      -- Subquery to get D ends;

ENAME
----------
CLARK
KING
MILLER

Types of Subqueries

In terms of the placement of the subquery, there are three types:

1. Nested Subquery: The subquery appears in the WHERE clause of the SQL.

2. Inline View: The subquery appears in the FROM clause of the SQL.

3. Scalar Subquery: The subquery appears in the SELECT clause of the SQL.

In terms of the way the subquery is parsed, there are two categories of subqueries:

1. Simple Subquery: This is the kind we saw above. A simple subquery is evaluated once only for each table.

2. Correlated Subquery: This is a type of nested subquery that uses columns from the outer query in its WHERE clause. A correlated subquery is evaluated once for each row.

We’ll look at the types of subqueries in close detail in the upcoming articles on Oratable.

Subquery Tips and Trivia

• You cannot have an ORDER BY clause with a nested subquery. See what happens when you try it:

SQL> select oe.ename from emp oe
  2  where oe.deptno =
  3      -- Subquery starts
  4         (select ie.deptno
  5          from emp ie
  6          where ie.ename = 'KING'
  7          order by ie.deptno)
  8      -- Subquery ends;
        order by ie.deptno)
        *
ERROR at line 7:
ORA-00907: missing right parenthesis

• You can nest as many as 255 levels of subqueries in the WHERE clause. (I hope you never need to hit that limit.)

• There is no limit on the number of subquery levels in the FROM clause of the top-level query.

• Get into the habit of using table aliases with subqueries. Get into the habit of using table aliases anyway – they make a world of difference to the readability of huge SQLs. In case of correlated subqueries, they are critical to keep the SQLs accurate. A sample of what may go wrong if you skip table aliases: The Curious Case of The Missing ORA-00904.

• Oracle resolves unqualified columns in the subquery by first looking at the tables in the subquery, then the tables in the outer query.