8.2 Multiple-Row Subqueries & Inline Views

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

8.2 Multiple-Row Subqueries & Inline Views

Key Takeaways Explain This Topic AI

Multiple-row subqueries return one or more rows (a list of values) to the outer query and must use multiple-row comparison operators: IN, ANY (or SOME), and ALL.

The IN operator tests for membership in the subquery result set and is logically identical to = ANY.

ANY / SOME compares a scalar value against each element in a set: > ANY means greater than the minimum; < ANY means less than the maximum.

ALL requires the condition to hold true against every element in the set: > ALL means greater than the maximum; < ALL means less than the minimum; <> ALL is identical to NOT IN.

An inline view is a subquery in the FROM clause that behaves as a dynamic virtual table, supporting column aliasing, join operations, and aggregations.

Last updated: August 2026

8.2 Multiple-Row Subqueries & Inline Views

When a subquery returns more than a single row of data, single-row comparison operators ( = , > , < ) cannot be used directly. Instead, Oracle SQL provides multiple-row operators designed to evaluate a scalar value against an entire collection (or list) of values. Furthermore, subqueries can be placed directly within the FROM clause to serve as transient, virtual tables known as Inline Views .

Mastering multiple-row operators and inline views is essential for solving complex analytical queries and is a primary focus of the 1Z0-071 examination.

Multiple-Row Comparison Operators

Oracle SQL supports three core multiple-row comparison operators: IN , ANY (synonym: SOME ), and ALL .

Operator Meaning / Evaluation Rule Equivalent Expression
IN Equal to any member in the subquery result list. = ANY
NOT IN Not equal to any member in the subquery result list. <> ALL

ANY / SOME Compares value to each value returned by the subquery; evaluates to TRUE if the condition is satisfied for at least one value. Combines with = , <> , > , < , >= , <=

ALL Compares value to every value returned by the subquery; evaluates to TRUE only if the condition is satisfied for all values. Combines with = , <> , > , < , >= , <=

Deep Dive: The ANY (and SOME) Operator

The keyword ANY (which has the exact same functionality as SOME ) must be preceded by a standard comparison operator ( = , <> , > , < , >= , <= ). +-------------------------------------------------------------------------+

| THE 'ANY' OPERATOR LOGIC |
+-------------------------------------------------------------------------+
| |
| Let Subquery S = { 2500, 4200, 8000 }
| |
| 1. salary > ANY ( S ) ==> salary > MIN(S) ==> salary > 2500 |
| (Greater than the smallest value in the list) |
| |
| 2. salary < ANY ( S ) ==> salary < MAX(S) ==> salary < 8000 |
| (Less than the largest value in the list) |
| |
| 3. salary = ANY ( S ) ==> salary IN ( 2500, 4200, 8000 ) |
| (Matches at least one value in the list) |
| |
| 4. salary <> ANY ( S ) ==> Matches any salary if S has >= 2 distinct |
| values (Because any value will differ from at least one element) |
+-------------------------------------------------------------------------+
Practical Example: > ANY -- Find all employees who earn more than at least one programmer ('IT_PROG')

-- This returns anyone whose salary exceeds the MINIMUM IT_PROG salary.

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG'
ORDER BY salary DESC;
Deep Dive: The ALL Operator

The ALL operator also pairs with standard comparison operators, but requires that the comparison hold true for every single value returned by the subquery. +-------------------------------------------------------------------------+

| THE 'ALL' OPERATOR LOGIC |
+-------------------------------------------------------------------------+
| |
| Let Subquery S = { 2500, 4200, 8000 }
| |
| 1. salary > ALL ( S ) ==> salary > MAX(S) ==> salary > 8000 |
| (Greater than the highest value in the list) |
| |
| 2. salary < ALL ( S ) ==> salary < MIN(S) ==> salary < 2500 |
| (Less than the lowest value in the list) |
| |
| 3. salary <> ALL ( S ) ==> salary NOT IN ( 2500, 4200, 8000 ) |
| (Does not match any value in the list) |
| |
| 4. salary = ALL ( S ) ==> Only TRUE if all elements of S are equal |
| and equal to salary (very rarely used) |
+-------------------------------------------------------------------------+
Practical Example: > ALL -- Find all employees who earn more than ALL programmers ('IT_PROG')

-- This returns only employees whose salary exceeds the MAXIMUM IT_PROG salary.

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG'
ORDER BY salary;
Comparison Logic Matrix: ANY vs. ALL

Expression Logical Equivalent Plain English Interpretation
x > ANY (subquery) x > (SELECT MIN(val) ...) Greater than the lowest subquery value
x < ANY (subquery) x < (SELECT MAX(val) ...) Less than the highest subquery value
x = ANY (subquery) x IN (subquery) Matches at least one subquery value
x > ALL (subquery) x > (SELECT MAX(val) ...) Greater than the highest subquery value
x < ALL (subquery) x < (SELECT MIN(val) ...) Less than the lowest subquery value
x <> ALL (subquery) x NOT IN (subquery) Does not match any subquery value

Exam Trap: Memorize the inverse relationship! Many exam candidates mistakenly guess that < ANY means "less than the minimum." Remember: < ANY means less than any member, so being less than the maximum satisfies the condition.

Inline Views (Subqueries in the FROM Clause)

An Inline View is a subquery embedded directly within the FROM clause of a SQL statement. Unlike a permanent database view created with CREATE VIEW , an inline view is not saved in the data dictionary; it exists only for the duration of the statement execution.

Why Use Inline Views?

Pre-aggregating Data: Summarize child rows before joining with parent rows to avoid Cartesian products or complicated GROUP BY clauses in the outer query.

Calculating Complex Ratios: Combine aggregated totals with detailed individual rows.

Column Aliasing & Computed Expressions: Give calculated expressions clean names that can be referenced throughout the outer query's WHERE , GROUP BY , and ORDER BY clauses.

Syntax and Mechanics -- Join an inline view of department averages with the employees table
SELECT
e.employee_id,
e.last_name,
e.salary,
e.department_id,
dept_summary.avg_dept_salary,
ROUND(e.salary - dept_summary.avg_dept_salary, 2) AS diff_from_dept_avg
FROM employees e
JOIN (
SELECT
department_id,
ROUND(AVG(salary), 2) AS avg_dept_salary,
COUNT(*) AS emp_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
) dept_summary
ON e.department_id = dept_summary.department_id
WHERE e.salary > dept_summary.avg_dept_salary

ORDER BY e.department_id, e.salary DESC; +-------------------------------------------------------------------------+

| INLINE VIEW JOIN ARCHITECTURE |
+-------------------------------------------------------------------------+
| |
| TABLE: employees (e) INLINE VIEW: dept_summary |
| +--------+--------+--------+ +---------------+---------------+ |
| | EMP_ID | DEPT_ID| SALARY | | DEPARTMENT_ID | AVG_DEPT_SAL | |
| +--------+--------+--------+ +---------------+---------------+ |
| | 100 | 90 | 24000 | JOIN | 90 | 19333.33 | |
| | 101 | 90 | 17000 | ======> | 60 | 5760.00 | |
| | 103 | 60 | 9000 | ON | 50 | 3475.55 | |
| +--------+--------+--------+ +---------------+---------------+ |
| |
+-------------------------------------------------------------------------+
Rules for Inline Views on the 1Z0-071 Exam

Column Aliases for Expressions: Any calculated expression or aggregate function in the inline view's SELECT list must be given a column alias if the outer query references it. -- ILLEGAL: Outer query references 'avg_sal' which was not aliased inside

SELECT dept_id, avg_sal
FROM (SELECT department_id AS dept_id, AVG(salary) FROM employees GROUP BY department_id);
- ORA-00904: "AVG_SAL": invalid identifier

-- LEGAL:

SELECT dept_id, avg_sal
FROM (SELECT department_id AS dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id);

Scope of Aliases: Table aliases declared on the inline view (e.g., ) dept_summary ) must be used by the outer query to qualify column references when disambiguating duplicate column names.

Top-N Filtering: Prior to Oracle 12c, inline views were the primary mechanism for Top-N queries using ROWNUM after ordering: -- Top 5 highest paid employees

SELECT rownum AS rank, employee_id, last_name, salary
FROM (
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 5;