Oracle SQL Subqueries: Single-Row, Multi-Row and Correlated — 1Z0-071 Exam Guide

1Z0-071 Exam Prep Chapter 8 Volume 2

Subqueries appear on the 1Z0-071 exam in three distinct forms — and each one has its own set of rules that the exam tests carefully. Getting a subquery wrong is not always obvious because the query runs without error but returns the wrong result, or in some cases returns no rows at all.

This post covers single-row subqueries, multi-row subqueries, and correlated subqueries — including the operator traps that cause most exam mistakes.

In this post

1. What a subquery is and where it can go
2. Single-row subqueries — one value returned
3. The single-row operator trap with multi-row results
4. Multi-row subqueries — IN, ANY, ALL
5. NULL inside a multi-row subquery — the silent trap
6. Correlated subqueries — running once per outer row
7. EXISTS and NOT EXISTS
8. Subqueries in the FROM clause — inline views
9. Quick reference
10. Practice questions

01 — What a subquery is and where it can go

A subquery is a SELECT statement nested inside another SQL statement. Oracle executes the inner query first, then uses the result in the outer query.

Subqueries can appear in the WHERE clause, the HAVING clause, the FROM clause, and the SELECT list. Each placement behaves slightly differently and the exam tests all of them.

-- Subquery in WHERE clause (most common on the exam)
SELECT last_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

-- Subquery in HAVING clause
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   AVG(salary) > (SELECT AVG(salary) FROM employees);

-- Subquery in SELECT list (scalar subquery)
SELECT last_name,
       (SELECT department_name
        FROM   departments d
        WHERE  d.department_id = e.department_id) AS dept
FROM   employees e;

Execution order: Oracle always runs the inner query first. The outer query never runs until the subquery has produced its result. This order is fixed — you cannot reverse it.

02 — Single-row subqueries — one value returned

A single-row subquery returns exactly one row with one column. You use single-row comparison operators with it: =, !=, >, <, >=, <=.

-- Find employees earning more than Abel
SELECT last_name, salary
FROM   employees
WHERE  salary > (SELECT salary
                 FROM   employees
                 WHERE  last_name = 'Abel');

-- Find employees in the same job as the lowest-paid employee
SELECT last_name, job_id
FROM   employees
WHERE  job_id = (SELECT job_id
                 FROM   employees
                 WHERE  salary = (SELECT MIN(salary)
                                  FROM   employees));

Nesting subqueries: Oracle allows subqueries nested inside subqueries. The innermost query runs first, working outward. The exam sometimes presents three levels of nesting — read from inside out to understand the logic.

03 — The single-row operator trap with multi-row results

If a subquery used with a single-row operator (=, > etc.) returns more than one row, Oracle raises ORA-01427: single-row subquery returns more than one row. This is one of the most tested error scenarios on the exam.

-- Raises ORA-01427 if more than one employee has the name 'King'
SELECT last_name, salary
FROM   employees
WHERE  salary = (SELECT salary
                 FROM   employees
                 WHERE  last_name = 'King');
-- Error: subquery returned two rows (two employees named King)

-- Fix: use IN instead of = to handle multiple rows
SELECT last_name, salary
FROM   employees
WHERE  salary IN (SELECT salary
                  FROM   employees
                  WHERE  last_name = 'King');

Exam rule: Single-row operators (= != > < >= <=) with a subquery that returns multiple rows = runtime error. Multi-row operators (IN ANY ALL) handle multiple rows correctly.

04 — Multi-row subqueries — IN, ANY, ALL

When a subquery can return more than one row, you need a multi-row operator. The exam tests all three and specifically asks you to distinguish between ANY and ALL.

-- IN: value matches any value in the subquery result
SELECT last_name, department_id
FROM   employees
WHERE  department_id IN (SELECT department_id
                         FROM   departments
                         WHERE  location_id = 1700);

-- ANY: value satisfies the condition for at least one value in the list
-- > ANY means: greater than the minimum value in the subquery result
SELECT last_name, salary
FROM   employees
WHERE  salary > ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG');

-- ALL: value satisfies the condition for every value in the list
-- > ALL means: greater than the maximum value in the subquery result
SELECT last_name, salary
FROM   employees
WHERE  salary > ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG');

Operator	Meaning	Equivalent to
= ANY	Matches at least one value	Same as IN
!= ALL	Does not match any value	Same as NOT IN
> ANY	Greater than at least one value	Greater than the minimum
> ALL	Greater than every value	Greater than the maximum
< ANY	Less than at least one value	Less than the maximum
< ALL	Less than every value	Less than the minimum

05 — NULL inside a multi-row subquery — the silent trap

This is the trap most candidates don't see coming. When a multi-row subquery result contains a NULL value, NOT IN returns no rows at all — not even rows that should clearly not be in the list.

-- Suppose the subquery returns: (10, 20, NULL)

-- IN works fine — NULL values in the list are simply ignored
SELECT last_name FROM employees
WHERE  department_id IN (10, 20, NULL);
-- Returns employees in dept 10 or 20 — NULL in list has no effect

-- NOT IN with a NULL in the result = zero rows returned
SELECT last_name FROM employees
WHERE  department_id NOT IN (10, 20, NULL);
-- Returns ZERO rows — because NOT IN expands to:
-- dept != 10 AND dept != 20 AND dept != NULL
-- dept != NULL is always UNKNOWN — so the whole condition is UNKNOWN

Critical exam rule: If the subquery used with NOT IN can return NULL (for example, a column that allows NULLs), the entire query returns zero rows. Fix it by adding WHERE column IS NOT NULL inside the subquery, or switch to NOT EXISTS.

-- Safe version — exclude NULLs from the subquery result
SELECT last_name FROM employees
WHERE  department_id NOT IN (SELECT department_id
                              FROM   departments
                              WHERE  department_id IS NOT NULL);

06 — Correlated subqueries — running once per outer row

A correlated subquery references a column from the outer query. Because of this, Oracle cannot run it once and reuse the result — it re-executes the subquery for every row the outer query processes.

-- Find employees earning more than the average salary in their own department
SELECT e.last_name, e.salary, e.department_id
FROM   employees e
WHERE  e.salary > (SELECT AVG(i.salary)
                   FROM   employees i
                   WHERE  i.department_id = e.department_id);
--                                          ^^^^^^^^^^^^^^^
-- This reference to the outer query is what makes it correlated
-- Oracle runs the inner SELECT once for each row of the outer query

How to spot a correlated subquery: Look for a column reference in the inner SELECT that uses an alias defined in the outer FROM clause. If the inner query references the outer table, it is correlated.

07 — EXISTS and NOT EXISTS

EXISTS checks whether a subquery returns any rows at all. It does not care about the actual values — only whether at least one row was found. This makes it faster than IN for large datasets and immune to the NULL problem that affects NOT IN.

-- Find departments that have at least one employee
SELECT department_name
FROM   departments d
WHERE  EXISTS (SELECT 1
               FROM   employees e
               WHERE  e.department_id = d.department_id);

-- Find departments with NO employees (NOT EXISTS)
SELECT department_name
FROM   departments d
WHERE  NOT EXISTS (SELECT 1
                   FROM   employees e
                   WHERE  e.department_id = d.department_id);

	IN / NOT IN	EXISTS / NOT EXISTS
What it checks	Whether value is in a list	Whether any row exists
NULL in subquery result	NOT IN breaks — returns zero rows	NOT EXISTS handles NULL safely
Subquery type	Non-correlated (usually)	Always correlated
SELECT list in subquery	Must return the comparison column	Convention is SELECT 1 — values don't matter

Exam convention: In EXISTS subqueries you will see SELECT 1, SELECT *, or SELECT 'X'. All three are equivalent — EXISTS only cares whether a row was found, not what the row contains. The exam may test whether you know this.

08 — Subqueries in the FROM clause — inline views

When a subquery appears in the FROM clause it acts as a temporary table for that query only. Oracle calls this an inline view. It is not stored anywhere — it exists only for the duration of the outer query.

-- Inline view: calculate department averages, then filter on them
SELECT dept_stats.department_id,
       dept_stats.avg_sal
FROM  (SELECT   department_id,
                AVG(salary) AS avg_sal
       FROM     employees
       GROUP BY department_id) dept_stats
WHERE dept_stats.avg_sal > 8000;

-- The inline view (dept_stats) is computed first
-- The outer query then filters its results

Why use an inline view: You cannot reference a column alias defined in the SELECT list in the same query's WHERE clause. An inline view solves this — define the alias in the inner query, then filter on it in the outer query.

09 — Quick reference

Subquery type	Returns	Operators to use	Key trap
Single-row	Exactly one row, one column	= != > < >= <=	ORA-01427 if more than one row returned
Multi-row	One column, multiple rows	IN NOT IN ANY ALL	NOT IN returns zero rows if subquery result contains NULL
Correlated	Re-executed per outer row	Any — commonly EXISTS	References outer query column — cannot run independently
Inline view	A derived table in FROM	N/A — it is a table source	Not stored — exists only for that query execution
Scalar subquery	Exactly one row, one column	Used in SELECT list	ORA-01427 if more than one row returned

10 — Practice questions

Q1 — The subquery below returns two rows. What happens when the outer query runs?

SELECT last_name FROM employees WHERE salary = (SELECT salary FROM employees WHERE department_id = 90);

A) Returns employees in department 90 B) Returns all employees with a matching salary C) Returns zero rows because no single match exists D) Raises ORA-01427: single-row subquery returns more than one row

Q2 — The MANAGER_ID column allows NULLs. One row in the subquery result is NULL. How many rows does this return?

SELECT last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees);

A) All employees who are not managers B) All employees C) Zero rows D) Raises an error because of the NULL

Q3 — Which statement correctly describes a correlated subquery?

A) It runs once and its result is reused for every outer row B) It must always use the EXISTS operator C) It references a column from the outer query and re-executes for each outer row D) It can only appear in the WHERE clause

Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set

19 chapters  ·  1,480 practice questions  ·  1,249 SQL examples
Chapter 8 covers every subquery pattern on the exam — single-row, multi-row, correlated, scalar, and inline views — with full worked examples and practice sets. Instant PDF download.

Get the full guide — $25 →

Published by https://www.apps-dba.com — Oracle DBA and certification resources.