Oracle SQL Subqueries: Single-Row, Multi-Row and Correlated

Oracle SQL Subqueries: Complete Guide

A subquery is a SELECT statement nested inside another SQL statement. Subqueries let you solve complex problems by breaking them into smaller, easier-to-understand pieces. They can appear in the WHERE clause, HAVING clause, FROM clause, and even the SELECT list.

Learning Objectives
By the end of this guide you will understand single-row, multi-row, and correlated subqueries, know how to combine them safely with comparison operators, and avoid the most common mistakes involving NULL values and operator choices.

1. Types of Subqueries

Type	Returns	Typical use
Single-row	Exactly one row and one column	Used with =, >, <, etc.
Multi-row	One column, multiple rows	Used with IN, ANY, ALL
Correlated	Re-executed for each outer row	References the outer query
Inline view	A temporary table in the FROM clause	Complex calculations or filtering

2. Single-Row Subqueries

A single-row subquery returns exactly one value. It is used with single-row comparison operators.

SELECT last_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) 
                 FROM   employees);

3. The Single-Row Operator Trap

If a subquery used with a single-row operator (=, >, <, etc.) returns more than one row, Oracle raises an error.

Common mistake: Using = with a subquery that returns multiple rows causes ORA-01427. Use IN instead when the subquery can return multiple values.

4. Multi-Row Subqueries

Use IN, ANY, or ALL when the subquery can return multiple rows.

-- IN
WHERE department_id IN (SELECT department_id 
                        FROM departments 
                        WHERE location_id = 1700);

-- ANY and ALL
WHERE salary > ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG');
WHERE salary > ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG');

5. NULL in Multi-Row Subqueries

When a subquery used with NOT IN returns a NULL value, the entire query returns zero rows — even if there are matching rows.

Important: NOT IN with a NULL in the subquery result always returns no rows. To avoid this, add WHERE column IS NOT NULL inside the subquery or switch to NOT EXISTS.

6. Correlated Subqueries

A correlated subquery references a column from the outer query. It runs once for every row returned by the outer query.

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

7. EXISTS and NOT EXISTS

EXISTS checks whether the subquery returns any rows at all. It is often faster than IN and safely handles NULL values.

SELECT department_name
FROM   departments d
WHERE  EXISTS (SELECT 1 
               FROM   employees e 
               WHERE  e.department_id = d.department_id);

8. Inline Views (Subqueries in FROM)

An inline view is a subquery in the FROM clause that acts like a temporary table for that query only.

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;

9. Interactive Practice Quiz

Q1. What happens when a single-row operator (=) is used with a subquery that returns two rows?

A. Returns all matching rows B. Returns zero rows C. Raises ORA-01427: single-row subquery returns more than one row D. Returns the first row only

Q2. A subquery used with NOT IN returns a NULL value. How many rows does the outer query return?

A. All rows that do not match the list B. Zero rows C. Only rows where the column is NULL D. An error

Q3. Which statement correctly describes a correlated subquery?

A. It runs once and the result is reused for every outer row B. It references a column from the outer query and re-executes for each outer row C. It can only be used in the SELECT list D. It must always use the EXISTS operator

Conclusion

Subqueries are one of the most useful features in Oracle SQL. Understanding the differences between single-row, multi-row, and correlated subqueries, along with the correct operators and NULL handling rules, will let you solve complex problems with clean and efficient queries.

Practice writing both non-correlated and correlated subqueries — they appear in almost every real-world reporting and data analysis task.