Subquery in Oracle

By /

A subquery is a query nested inside another query, typically used to perform more complex searches by querying results from another query. The subquery can return a single value, a list of values, or a table of results. It is often used with SELECT, INSERT, UPDATE, and DELETE statements.

Types of Subqueries:

  1. Single Row Subquery: Returns a single row.
  2. Multiple Row Subquery: Returns multiple rows.
  3. Multiple Column Subquery: Returns multiple columns.
  4. Correlated Subquery: Refers to columns in the outer query.

1. Single Row Subquery

A Single Row Subquery returns one row. It is commonly used with comparison operators such as =, <, >, <=, >=, and <>.

Example: Find employees whose salary is greater than the average salary of department 60.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department_id = 60);

  • Explanation: The subquery calculates the average salary for department 60. The outer query retrieves employees whose salary is greater than this value.

2. Multiple Row Subquery

A Multiple Row Subquery returns multiple rows. It is often used with operators like IN, ANY, or ALL.

Example: Find employees who work in the same department as employees with a job ID of ‘IT_PROG’.

SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
                        FROM employees
                        WHERE job_id = ‘IT_PROG’);

  • Explanation: The subquery retrieves the department IDs where employees have the job ID ‘IT_PROG’. The outer query finds all employees who work in those departments.

3. Multiple Column Subquery

A Multiple Column Subquery returns multiple columns. It is used when more than one column is needed from the subquery.

Example: Find employees who earn the same salary and work in the same department as employee Steven King.

SELECT first_name, last_name, department_id, salary
FROM employees
WHERE (salary, department_id) IN (SELECT salary, department_id
                                  FROM employees
                                  WHERE first_name = ‘Steven’
                                  AND last_name = ‘King’);

  • Explanation: The subquery retrieves the salary and department of Steven King. The outer query finds employees with matching salary and department.

4. Correlated Subquery

A Correlated Subquery references columns from the outer query and is re-executed for each row of the outer query.

Example: Find employees who earn more than the average salary in their department.

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

  • Explanation: The subquery is correlated because it uses e.department_id from the outer query. For each employee, the subquery calculates the average salary of their department, and the outer query retrieves those who earn more than that average.

5. Subquery in the FROM Clause

A subquery can also be placed in the FROM clause, treating its result as a temporary table.

Example: Find the average salary for each department and only display those with an average salary greater than 10,000.

SELECT department_id, avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department_id)
WHERE avg_salary > 10000;

  • Explanation: The inner query calculates the average salary per department. The outer query filters those departments where the average salary is greater than 10,000.

6. Subquery with EXISTS

The EXISTS operator is used to check whether a subquery returns any rows.

Example: Find departments that have at least one employee.

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

  • Explanation: The subquery checks if there are employees in each department. The outer query retrieves departments where the subquery returns rows.

ANY and ALL Operators in Subqueries

The ANY and ALL operators are used in conjunction with subqueries to compare values in the outer query with one or more values returned by the subquery. These operators allow comparisons to be made with multiple rows returned by a subquery.

1. ANY Operator

  • Purpose: The ANY operator compares a value to any value in a list or subquery. If any of the values meet the condition, the row is returned.
  • It is often used with comparison operators such as =, >, <, >=, <=.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name comparison_operator ANY (subquery);

  • The condition is true if at least one value from the subquery satisfies the comparison.

Example:

Find employees whose salary is greater than any salary in department 60.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > ANY (SELECT salary
                    FROM employees
                    WHERE department_id = 60);

  • Explanation:
    • The subquery retrieves all the salaries of employees in department 60.
    • The outer query selects employees whose salary is greater than at least one of the salaries returned by the subquery.

If department 60 has salaries like 6000, 7000, and 8000, then an employee with a salary of 6500 in any other department would be returned because their salary is greater than 6000 (one of the salaries in department 60).

2. ALL Operator

  • Purpose: The ALL operator compares a value to all values in a list or subquery. It returns true only if the comparison is true for every value returned by the subquery.
  • It is also used with comparison operators like =, >, <, >=, <=.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name comparison_operator ALL (subquery);

  • The condition is true only if the value in the outer query satisfies the comparison for all values from the subquery.

Example:

Find employees whose salary is greater than all salaries in department 60.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > ALL (SELECT salary
                    FROM employees
                    WHERE department_id = 60);

  • Explanation:
    • The subquery retrieves all salaries of employees in department 60.
    • The outer query returns employees whose salary is greater than every salary from department 60.

If department 60 has salaries like 6000, 7000, and 8000, then an employee must have a salary greater than 8000 (the highest salary) to be returned.

Comparison of ANY vs. ALL

OperatorConditionReturns
ANYTrue if any value from the subquery satisfies the conditionIf at least one of the subquery results matches the condition
ALLTrue if all values from the subquery satisfy the conditionOnly if every value from the subquery matches the condition

Practical Example with HR Schema

Suppose we have the following subquery that retrieves the salaries of employees in department 60:

SELECT salary FROM employees WHERE department_id = 60;

Let’s assume department 60 has three employees with salaries of 6000, 7000, and 8000.

  1. ANY Example:
    • Query:
      SELECT first_name, last_name, salary
      FROM employees
      WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 60);
  • Result: This query will return employees whose salary is greater than at least one of the salaries from department 60. For example, if an employee has a salary of 6500, they will be returned because 6500 > 6000 (but not greater than 7000 or 8000).
  1. ALL Example:
    • Query:
      SELECT first_name, last_name, salary
      FROM employees
      WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 60);
  • Result: This query will return employees whose salary is greater than all salaries from department 60. Only employees with a salary greater than 8000 (the highest salary in department 60) will be returned.

Summary:

  • ANY: Returns true if any value from the subquery satisfies the condition. It is more lenient and will return rows if even one comparison is true.
  • ALL: Returns true only if all values from the subquery satisfy the condition. It is stricter and returns rows only if the comparison holds true for every value in the subquery results.

These operators are powerful for performing flexible comparisons when the result set from the subquery contains multiple rows.

Summary:

  • Single Row Subquery: Returns one row and is used with comparison operators.
  • Multiple Row Subquery: Returns multiple rows and is used with IN, ANY, or ALL.
  • Multiple Column Subquery: Returns multiple columns and allows comparing multiple columns simultaneously.
  • Correlated Subquery: Refers to outer query columns and is re-evaluated for each row.
  • Subquery in FROM Clause: Treats the subquery result as a temporary table.
  • Subquery with EXISTS: Checks if the subquery returns any rows.

1️⃣ Subquery in SELECT clause (Scalar Subquery)

👉 Used when the subquery returns a single value per row

Example:
Display each customer along with average account balance of all customers

SELECT
    c.customer_id,
    c.name,
    (SELECT AVG(balance) FROM accounts) AS avg_bank_balance
FROM customers c;

📌 Use case:
Compare individual customer data with overall bank metrics.

2️⃣ Subquery in FROM clause (Inline View)

👉 Used when the subquery returns a result set (table-like)

Example:
Find customers whose total balance is greater than 1,00,000

SELECT customer_id, total_balance
FROM (
    SELECT customer_id, SUM(balance) AS total_balance
    FROM accounts
    GROUP BY customer_id
)
WHERE total_balance > 100000;

📌 Use case:
Pre-aggregations, reporting, complex calculations.

3️⃣ Subquery in WHERE clause (Most Common)

👉 Used for filtering rows

Example:
Find customers who have accounts

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM accounts
);

📌 Use case:
Filtering based on related table data.

4️⃣ Subquery in HAVING clause

👉 Used to filter grouped data

Example:
Find customers whose total balance is greater than the average total balance

SELECT customer_id, SUM(balance) AS total_balance
FROM accounts
GROUP BY customer_id
HAVING SUM(balance) > (
    SELECT AVG(balance)
    FROM accounts
);

📌 Use case:
Comparing group-level metrics.

🔑 Interview One-Line Summary

ClausePurpose
SELECTReturn a calculated value
FROMCreate a derived table
WHEREFilter rows
HAVINGFilter groups
Scroll to Top