8.2 Multiple-Row Subqueries & Inline Views

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.

---

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

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?

1. Pre-aggregating Data: Summarize child rows before joining with parent rows to avoid Cartesian products or complicated GROUP BY clauses in the outer query.
2. Calculating Complex Ratios: Combine aggregated totals with detailed individual rows.
3. 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

1. 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);
   

2. 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.
3. 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;