Oracle SQL Certification 1Z0-071 Course | Section 12: Using Subqueries

Subqueries are one of the most important and exam-critical topics in Oracle SQL. They allow you to place a query inside another SQL statement, dramatically expanding what you can do with filtering, comparison, and data manipulation.

Subqueries are one of the most important and exam-critical topics in Oracle SQL. They allow you to place a query inside another SQL statement, dramatically expanding what you can do with filtering, comparison, and data manipulation.

A subquery may look simple, but it unlocks advanced logic such as:

  • Comparing rows against group averages
  • Updating data based on calculated values
  • Checking whether related records exist
  • Structuring complex queries cleanly

If you truly understand subqueries, your SQL skills jump to a completely different level.

What Are Subqueries?

A subquery is a query nested inside another SQL statement.

You can use subqueries inside:

  • SELECT
  • FROM
  • WHERE
  • INSERT
  • UPDATE
  • DELETE
  • MERGE
  • CREATE TABLE
  • CREATE VIEW
Key Principle

Most subqueries should be able to run independently.
If you copy the subquery and execute it alone, it should work.

Why Use Subqueries?

They allow you to:

  • Build complex dynamic queries
  • Move data between tables
  • Create calculated comparisons
  • Generate temporary result sets
  • Simplify logical conditions
Types of Subqueries

There are several main types:

🔹 Single-Row Subqueries

Return exactly one row.

🔹 Multiple-Row Subqueries

Return multiple rows.

🔹 Multiple-Column Subqueries

Return more than one column.

🔹 Correlated Subqueries

Reference columns from the outer query.

🔹 Scalar Subqueries

Return a single value (one row, one column).

Understanding the difference between these types is critical for the exam.

Query Data Using Correlated Subqueries

Let’s start simple.

Example 1 – Employees earning more than the overall average:
SELECT first_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

This works because the subquery returns one value.

Correlated Subqueries

Now we level up.

Suppose we want to compare each employee’s salary with the average salary for their job.

Now the subquery must reference the outer query:

SELECT e.first_name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE job_id = e.job_id
);

Here’s what makes it correlated:

  • The subquery references e.job_id
  • It runs once for each row of the outer query
  • Table aliases are mandatory
Important

Correlated subqueries:

  • Execute repeatedly
  • Can impact performance
  • Depend on outer query values

They are powerful but must be used carefully.

Updating Rows Using Correlated Subqueries

Subqueries are not limited to SELECT.

You can use them inside UPDATE statements.

Example: Increase salaries for employees earning below their job average.

UPDATE employees e
SET salary = salary * 1.1
WHERE salary < (
    SELECT AVG(salary)
    FROM employees
    WHERE job_id = e.job_id
);
Key Concepts
  • The subquery is correlated
  • The WHERE clause prevents updating all rows
  • Averages change after updates
  • Same logic applies to DELETE

âš  Always test SELECT first before using UPDATE with correlated logic.

EXISTS and NOT EXISTS

The EXISTS operator checks whether a subquery returns rows.

It does not compare values.
It returns TRUE or FALSE.

Example – Employees who changed departments:
SELECT e.employee_id
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM job_history j
    WHERE j.employee_id = e.employee_id
);

If at least one matching row exists → TRUE.

NOT EXISTS

Returns rows where no related record exists.

SELECT e.employee_id
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM job_history j
    WHERE j.employee_id = e.employee_id
);
Why EXISTS Is Powerful
  • Efficient for large datasets
  • Stops searching once match is found
  • Ideal for relational checks
Using the WITH Clause

Instead of embedding a subquery inside FROM, you can define it first:

WITH avg_salary AS (
    SELECT job_id, AVG(salary) avg_sal
    FROM employees
    GROUP BY job_id
)
SELECT e.first_name, e.salary
FROM employees e
JOIN avg_salary a
ON e.job_id = a.job_id
WHERE e.salary > a.avg_sal;
Why Use WITH?
  • Improves readability
  • Organizes complex queries
  • Acts like a temporary inline view
  • Does NOT create a permanent object

These are extremely helpful in real-world SQL projects.

Single-Row Subqueries

A single-row subquery must return exactly one row.

Valid operators:

  • =
  • >
  • <
  • >=
  • <=
  • <>
What Happens If Multiple Rows Are Returned?

You get:

ORA-01427: single-row subquery returns more than one row
How to Guarantee One Row

Use aggregate functions:

SELECT first_name
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

You can also use ROWNUM, but be cautious — it may produce unpredictable results without ORDER BY.

Multiple-Row Subqueries

If a subquery returns multiple rows, you cannot use =.

This will fail:

WHERE salary = (SELECT salary FROM employees WHERE job_id = 'IT_PROG');
Correct Operators for Multiple Rows
🔹 IN
WHERE salary IN (
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
);

IN works like multiple OR comparisons.

🔹 ANY (or SOME)
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
);

TRUE if condition matches at least one value.

🔹 ALL
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
);

TRUE only if condition matches every value.

Difference Between ANY and ALL
OperatorMeaning
ANYAt least one value matches
ALLMust match every value

Understanding this logic is heavily tested on the exam.

Exam-Critical Rules
  • Subqueries can appear in many clauses.
  • Single-row subqueries must return exactly one row.
  • Multiple-row subqueries require IN, ANY, or ALL.
  • Correlated subqueries reference outer query columns.
  • EXISTS returns TRUE/FALSE, not values.
  • WITH improves readability but creates no object.
  • Test SELECT before running UPDATE or DELETE with subqueries.
Final Thoughts

Subqueries are one of the most powerful features in SQL.

They allow you to:

  • Compare rows to dynamic calculations
  • Modify data intelligently
  • Build layered logic
  • Write cleaner, more maintainable queries

Now already involved in this SQL journey, you can subscribe to the YouTube channel and maybe subscribe to newsletter below?

Join ForgeFuse Newsletter