8.1 Single-Row Subqueries

8.1 Single-Row Subqueries

In relational database management systems, solving complex business questions often requires a multi-step querying approach: first determining an unknown intermediate value (such as the company-wide average salary or the department ID of a specific employee), and then using that calculated value to filter or shape the final result set. In Oracle SQL, a subquery (also referred to as an inner query or nested query) is a SELECT statement embedded within another SQL statement (the outer query or parent query).

Subqueries are categorized primarily by the cardinality of the result set they return to the outer query. The most fundamental category is the Single-Row Subquery.

---

Definition and Mechanics of Single-Row Subqueries

A single-row subquery is a nested query that returns at most one row containing one column to its parent statement. Because it produces a single atomic value, a single-row subquery is often referred to as a scalar subquery.

Execution Flow

In standard (uncorrelated) single-row subqueries, execution proceeds strictly from the inside out:

1. The inner subquery executes first and executes only once.
2. The scalar value generated by the inner subquery is passed directly to the outer parent query.
3. The outer query executes using the returned scalar value to evaluate its filtering predicates or projection expressions.

+-------------------------------------------------------------------------+
|                        SINGLE-ROW EXECUTION FLOW                        |
+-------------------------------------------------------------------------+
|                                                                         |
|  OUTER QUERY:                                                           |
|  SELECT first_name, last_name, salary                                   |
|  FROM employees                                                         |
|  WHERE salary > [ ??? ] -----------------------+                        |
|                                                | Passes scalar value    |
|                                                | (e.g., 6461.83)        |
|  INNER SUBQUERY:                               |                        |
|  (SELECT AVG(salary) FROM employees) ----------+                        |
|   1. Executes ONCE                                                      |
|   2. Computes aggregate salary: 6461.83                                 |
|   3. Passes scalar result 6461.83 to outer WHERE clause                 |
|                                                                         |
|  FINAL EVALUATION:                                                      |
|  SELECT first_name, last_name, salary                                   |
|  FROM employees                                                         |
|  WHERE salary > 6461.83;                                                |
+-------------------------------------------------------------------------+

---

Valid Single-Row Comparison Operators

Because a single-row subquery returns a single scalar value, the outer query must evaluate it using single-row comparison operators. Using multiple-row operators (such as IN, ANY, or ALL) with a single-row subquery is syntactically valid, but using a single-row operator with a multiple-row subquery causes runtime failure.

Exam Tip: Subqueries must always be enclosed within parentheses (). Placing the subquery on the right side of the comparison operator is standard practice and greatly improves query readability, although Oracle SQL allows subqueries on either side of the operator (e.g., WHERE (SELECT AVG(salary) FROM emp) < salary is syntactically legal).

---

Subquery Placement: WHERE, HAVING, and SELECT

Single-row subqueries can be placed in virtually any clause of a SQL statement where an expression or literal value is valid.

1. In the WHERE Clause

The most common placement of a single-row subquery is in the WHERE clause to filter individual rows before grouping or aggregation takes place.

-- Retrieve all employees whose salary exceeds the company-wide average salary
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)
ORDER BY salary DESC;

You can also combine multiple single-row subqueries within the same WHERE clause using logical AND / OR operators:

-- Retrieve employees who work in the same department as 'Taylor'
-- AND earn more than employee 145
SELECT employee_id, last_name, department_id, salary
FROM employees
WHERE department_id = (
    SELECT department_id 
    FROM employees 
    WHERE last_name = 'Taylor' AND employee_id = 176
)
AND salary > (
    SELECT salary 
    FROM employees 
    WHERE employee_id = 145
);

2. In the HAVING Clause

When filtering groups of rows based on aggregate conditions, single-row subqueries can be placed in the HAVING clause. The subquery itself may compute an aggregate value that is compared against the group function of the outer query.

-- Display department IDs and their minimum salary, but only for departments
-- whose minimum salary is greater than the minimum salary in Department 50
SELECT department_id, MIN(salary) AS min_dept_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
)
ORDER BY min_dept_sal;

3. In the SELECT Clause (Scalar Subquery Expressions)

A subquery placed directly in the SELECT list is known as a Scalar Subquery Expression. It must return exactly one column and at most one row for every row processed by the outer query.

-- Display each employee's salary alongside the overall company average
-- and the difference between their salary and that average
SELECT 
    last_name,
    salary,
    ROUND((SELECT AVG(salary) FROM employees), 2) AS company_avg,
    ROUND(salary - (SELECT AVG(salary) FROM employees), 2) AS diff_from_avg
FROM employees
WHERE department_id = 60;

---

Combining Aggregate and Single-Row Functions in Subqueries

Subqueries frequently combine single-row functions (such as UPPER, SUBSTR, ROUND) and aggregate functions (MIN, MAX, AVG, COUNT, SUM).

-- Find all employees hired in the earliest hire year
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE TO_CHAR(hire_date, 'YYYY') = (
    SELECT MIN(TO_CHAR(hire_date, 'YYYY'))
    FROM employees
);

Nesting Subqueries

Oracle SQL supports nesting subqueries within other subqueries to solve multi-layered problems. Oracle allows nesting subqueries up to 255 levels deep in a WHERE clause.

-- Find employees who work in the department with the lowest average salary
SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) = (
        SELECT MIN(AVG(salary))
        FROM employees
        GROUP BY department_id
    )
);

---

Common Runtime Errors and Troubleshooting

Understanding how Oracle reacts when single-row subquery assumptions are violated is heavily tested on the 1Z0-071 exam.

+-------------------------------------------------------------------------+
|                   SUBQUERY RUNTIME ERROR TAXONOMY                       |
+-------------------------------------------------------------------------+
|                                                                         |
|  Case 1: Subquery returns 2+ rows to single-row operator (=, >, <)      |
|  --> ERROR: ORA-01427: single-row subquery returns more than one row   |
|                                                                         |
|  Case 2: Subquery returns 0 rows to single-row operator                 |
|  --> NO ERROR: Subquery evaluates to NULL                               |
|  --> Predicate 'WHERE salary > NULL' evaluates to UNKNOWN (0 rows out)  |
|                                                                         |
|  Case 3: Subquery returns 2+ columns to single-column operator          |
|  --> ERROR: ORA-00913: too many values                                  |
|                                                                         |
+-------------------------------------------------------------------------+

1. ORA-01427: single-row subquery returns more than one row

This is the most famous subquery error in Oracle SQL. It occurs when a query uses a single-row comparison operator (=, >, <, <=, >=, <>), but the subquery returns two or more rows at execution time.

-- ERROR EXAMPLE:
-- If there are multiple employees named 'King' (e.g., Steven King and Janette King),
-- the subquery returns two department_ids (90 and 80).
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = (
    SELECT department_id 
    FROM employees 
    WHERE last_name = 'King'
);
-- ORA-01427: single-row subquery returns more than one row

Troubleshooting & Solutions for ORA-01427:

1. Switch to a Multiple-Row Operator: If multiple values are valid business possibilities, replace = with IN, or replace > with > ALL / > ANY.

   WHERE department_id IN (SELECT department_id FROM employees WHERE last_name = 'King')
   

2. Enforce Uniqueness in the Subquery: Filter by primary key or unique column (e.g., WHERE employee_id = 100).
3. Apply an Aggregate Function: Use MIN, MAX, or AVG to guarantee a scalar return value:

   WHERE department_id = (SELECT MAX(department_id) FROM employees WHERE last_name = 'King')
   

2. Zero-Row Subquery Returns (Evaluating to NULL)

If an uncorrelated subquery executes and finds no matching rows, it does not raise an error. Instead, the subquery evaluates to NULL.

-- The subquery finds no employee named 'NonExistent'
-- The subquery returns NULL
-- The outer WHERE clause becomes: WHERE salary > NULL
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
    SELECT salary 
    FROM employees 
    WHERE last_name = 'NonExistent'
);
-- Result: 0 rows selected (no error raised!)

In SQL three-valued logic, any comparison with NULL using arithmetic or comparison operators (salary > NULL, salary = NULL, salary <> NULL) yields UNKNOWN. Because WHERE clauses only retain rows for which the predicate evaluates to TRUE, the outer query returns no rows without error.

3. ORA-00913: too many values

If the subquery attempts to project more than one column when the outer comparison expects a scalar value, Oracle raises ORA-00913: too many values during the parsing/compilation phase.

-- PARSE ERROR:
SELECT * FROM employees
WHERE salary = (SELECT salary, department_id FROM employees WHERE employee_id = 100);
-- ORA-00913: too many values