8.1 Single-Row Subqueries

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.1 Single-Row Subqueries

Key Takeaways Explain This Topic AI

A single-row subquery returns at most one row (one column and one row, yielding a scalar value) to the enclosing parent query.

Single-row subqueries must use single-row comparison operators: =, >, <, >=, <=, and <> (or !=, ^=).

Single-row subqueries can be placed in the WHERE, HAVING, SELECT (scalar subquery expressions), and FROM clauses, as well as DML statements.

If a single-row subquery returns more than one row at runtime, Oracle raises the runtime error ORA-01427: single-row subquery returns more than one row.

If an un-correlated single-row subquery returns zero rows, it evaluates to NULL; comparing any value to NULL via single-row operators evaluates to UNKNOWN, discarding the candidate row.

Last updated: August 2026

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:

The inner subquery executes first and executes only once .

The scalar value generated by the inner subquery is passed directly to the outer parent query.

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.

Operator Meaning Example Clause
= Equal to WHERE department_id = (SELECT department_id FROM ...)
> Greater than WHERE salary > (SELECT AVG(salary) FROM ...)
< Less than WHERE hire_date < (SELECT hire_date FROM ...)
>= Greater than or equal to WHERE salary >= (SELECT MAX(salary) FROM ...)
<= Less than or equal to WHERE salary <= (SELECT MIN(salary) FROM ...)
<> or != or ^= Not equal to WHERE job_id <> (SELECT job_id FROM ...)

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:

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')

Enforce Uniqueness in the Subquery: Filter by primary key or unique column (e.g., WHERE employee_id = 100 ).

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