What is a Subquery?
A subquery, also known as a nested query or inner query, is a SQL query embedded within the WHERE, FROM, or SELECT clause of another SQL statement (the outer query or main query).
Its core purpose is to retrieve data that will be used by the main query to perform more complex operations, such as filtering, comparing, or defining a dataset on-the-fly.
Single-Row Subqueries
A single-row subquery returns exactly one row of results. It is typically used with single-row comparison operators such as =, >, >=, <, <=, and <>.
DECLARE
l_employee_name employees.last_name%TYPE;
l_salary employees.salary%TYPE;
BEGIN
-- Outer query uses '=' because the subquery returns a single value (the max salary)
SELECT last_name, salary INTO l_employee_name, l_salary
FROM employees
WHERE salary = (
-- Single-Row Subquery: Finds the maximum salary in the entire table.
SELECT MAX(salary)
FROM employees
);
DBMS_OUTPUT.PUT_LINE('Highest paid employee is ' || l_employee_name || ' with salary: ' || l_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More than one employee has the maximum salary.');
END;
/
Multiple-Row Subqueries
A multiple-row subquery returns more than one row. It must be used with multiple-row comparison operators like IN, NOT IN, ANY, ALL.
Use IN operator with subqueries to filter based on a dynamic list of values.
DECLARE
CURSOR c_high_earners IS
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id IN (
-- Multiple-Row Subquery: Returns a list of department IDs for 'IT' and 'Finance'.
SELECT department_id
FROM departments
WHERE department_name IN ('IT', 'Finance')
);
BEGIN
FOR emp_rec IN c_high_earners LOOP
DBMS_OUTPUT.PUT_LINE('Employee ' || emp_rec.last_name || ' works in a high-earning department.');
END LOOP;
END;
/
Multiple-Column Subqueries
A multiple-column subquery returns more than one column. This is often used to compare a set of values against another set.
BEGIN
-- Update employees in a specific location to have the same job and salary as a reference employee.
UPDATE employees
SET (job_id, salary) = (
-- Multiple-Column Subquery: Returns two columns (job_id, salary).
SELECT job_id, salary
FROM employees
WHERE employee_id = 110 -- Reference employee
)
WHERE department_id = (
SELECT department_id
FROM departments
WHERE location_id = 1700 -- Seattle
);
COMMIT;
END;
/
Correlated subqueries are powerful for row-by-row comparisons but can impact performance.
DECLARE
CURSOR c_emp_dept_stats IS
SELECT e.employee_id, e.last_name, e.department_id,
e.salary,
-- Correlated Subquery: For each employee (e), calculate the avg salary for their department.
(SELECT AVG(salary)
FROM employees d
WHERE d.department_id = e.department_id) AS dept_avg_salary
FROM employees e;
BEGIN
FOR rec IN c_emp_dept_stats LOOP
IF rec.salary > rec.dept_avg_salary THEN
DBMS_OUTPUT.PUT_LINE(rec.last_name || ' earns more than their department average.');
END IF;
END LOOP;
END;
/
EXISTS Operator
The EXISTS operator is used in the WHERE clause to test for the existence of rows in a subquery. It returns TRUE if the subquery returns at least one row.
EXISTS is highly efficient because it stops processing as soon as it finds a match.
BEGIN
-- Delete departments that have no employees.
DELETE FROM departments d
WHERE NOT EXISTS (
-- Correlated Subquery with EXISTS: Checks if there are any employees for the current department (d).
SELECT 1
FROM employees e
WHERE e.department_id = d.department_id
);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' empty departments deleted.');
END;
/
NOT EXISTS Operator
The logical opposite of EXISTS. It returns TRUE if the subquery returns no rows.
NOT EXISTS is the preferred and safest way to check for the absence of related records, especially compared to NOT IN which can behave unexpectedly with NULL values.
DECLARE
CURSOR c_managers_without_team IS
SELECT employee_id, last_name
FROM employees m
WHERE m.job_id LIKE '%MAN%' -- Identify managers
AND NOT EXISTS (
-- Find managers for whom no other employee reports to them.
SELECT 1
FROM employees e
WHERE e.manager_id = m.employee_id
);
BEGIN
FOR mgr IN c_managers_without_team LOOP
DBMS_OUTPUT.PUT_LINE('Manager ' || mgr.last_name || ' has no direct reports.');
END LOOP;
END;
/
WITH Clause (Common Table Expressions)
The WITH clause (or CTE) allows you to define a named subquery block at the start of a statement. This improves readability (DRY Principle) and performance, as the database can materialize the result set.
DECLARE
l_total_salary NUMBER;
BEGIN
-- Using a WITH clause to create a temporary result set 'DeptSalaries'
WITH DeptSalaries AS (
-- Subquery defined once and referenced twice below.
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
)
SELECT SUM(total_salary) INTO l_total_salary
FROM DeptSalaries
WHERE total_salary > (SELECT AVG(total_salary) FROM DeptSalaries); -- Self-reference within the CTE
DBMS_OUTPUT.PUT_LINE('Total salary of above-average departments: ' || l_total_salary);
END;
/
Inline Views
An inline view is a subquery in the FROM clause of a main query. It treats the result set of the subquery as a temporary table for the duration of the query.
Inline views enable complex joins and aggregations without creating physical tables.
DECLARE
CURSOR c_top_earners IS
-- The main query selects from an Inline View 'RankedEmployees'
SELECT employee_id, last_name, salary, salary_rank
FROM (
-- Inline View: A subquery that ranks employees by salary.
SELECT employee_id, last_name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
) RankedEmployees
WHERE salary_rank <= 5; -- Filter on the column defined in the inline view.
BEGIN
FOR emp IN c_top_earners LOOP
DBMS_OUTPUT.PUT_LINE('Top earner rank #' || emp.salary_rank || ': ' || emp.last_name);
END LOOP;
END;
/
Why are Subqueries Important?
Enhanced Modularity and Readability
DRY Principle: Subqueries allow you to decompose a complex problem into simpler, logical parts. A well-named CTE (WITH clause) makes the code self-documenting.
Dynamic Data Filtering
They enable comparisons based on dynamic, data-driven values (e.g., “find employees who earn more than the average”) rather than hard-coded literals. This implements the Strategy Pattern at the SQL level.
Foundation for Set-Based Operations
Subqueries are the primary tool for performing powerful set-based operations (existence checks with EXISTS, comparisons with IN/ALL/ANY), which are more efficient than procedural row-by-row processing.
Advanced Nuances
NOT IN and NULL Pitfalls
A major “gotcha”: If the result set of a NOT IN subquery contains a single NULL value, the entire query will return no rows.
NOT IN is equivalent to a series of <> comparisons, and any comparison with NULL yields UNKNOWN. Always prefer NOT EXISTS for existence checks.
While powerful, correlated subqueries can be performance bottlenecks as they execute once per row of the outer query. Proper indexing on the join columns is critical. For complex cases, consider rewriting them as JOIN operations.
Scalar Subquery Caching
In certain contexts (e.g., when a correlated subquery in the SELECT list uses a deterministic function), the Oracle optimizer may cache the result of the subquery for repeated input values, improving performance.
How This Fits the Roadmap
Within the “Advanced SQL Features” section of the PL/SQL Mastery roadmap, Subqueries are a foundational pillar.
Direct Prerequisites For:
- Advanced Analytics - Window functions often rely on inline views
- Hierarchical Queries - Building recursive WITH clauses (CTEs)
- Dynamic SQL - Constructing subquery logic as strings
- Query Optimization - Understanding when subqueries are unnested into joins
Unlocks:
- Set-based thinking
- Efficient database-centric PL/SQL code
- Advanced query transformer understanding
- Optimizer behavior prediction