Oracle 1Z0-116: Subqueries

What the domain covers

Subqueries are queries nested inside another SQL statement. This domain tests single-row and multiple-row subqueries, IN, ANY, ALL, EXISTS, correlated subqueries, and scalar subqueries.

Core rules

• A single-row subquery returns at most one row and one column; use it with =, >, <, >=, <=, <>.
• A multiple-row subquery returns one or more rows; use IN, ANY, ALL, or EXISTS.
• > ANY means greater than the minimum; > ALL means greater than the maximum. = ANY is equivalent to IN.
• EXISTS tests whether the subquery returns any row and stops at the first match; it is often used with correlated subqueries.
• A correlated subquery references a column from the outer query and is evaluated for each outer row.
• A scalar subquery returns one row and one column and can appear in SELECT, WHERE, or HAVING.

Worked example

SELECT last_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); Reasoning: the inner query returns one numeric value, the average salary of all employees. The outer query compares each employee salary with that single value. This is an uncorrelated subquery because the inner query does not reference the outer employees table. If the inner query returned more than one row, the > comparison would raise a single-row subquery error.

Traps

Do not use = with a subquery that can return multiple rows. NOT IN with a subquery that returns NULL can produce no rows. A subquery in the SELECT list must be scalar. Correlated subqueries can be slower because they may run once per outer row.