Subquery FAQs

1. What is a Subquery in Oracle?

A Subquery is a query written inside another SQL statement.
Also called:

·        Inner Query

·        Nested Query

Example:

SELECT employee_id, salary

FROM employees

WHERE salary > (

    SELECT AVG(salary) FROM employees

);

·        Inner query calculates average salary

·        Outer query fetches employees earning more than average

2. Where can Subqueries be used?

·        WHERE clause

·        SELECT clause

·        FROM clause

·        HAVING clause

·        INSERT, UPDATE, DELETE statements

3. Types of Subqueries

Single-Row Subquery

·        Returns exactly one row

·        Uses operators: =, >, <, >=, <=
Example:

SELECT *

FROM employees

WHERE salary > (

    SELECT AVG(salary) FROM employees

);

Multi-Row Subquery

·        Returns multiple rows

·        Uses operators: IN, ANY, ALL, EXISTS
Example:

SELECT *

FROM employees

WHERE department_id IN (

    SELECT department_id FROM departments WHERE location_id = 1700

);

Correlated Subquery

·        Inner query depends on outer query

·        Executed once per outer row
Example:

SELECT e.employee_id, e.salary

FROM employees e

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = e.department_id

);

Nested Subquery

·        Subquery inside another subquery

4. Subquery vs Join

Feature	Subquery	Join
Readability	Often simple	May be complex
Performance	Sometimes slower	Often faster
Correlation	Possible	Not applicable
Execution	Inner query executed first	Tables combined first

Oracle optimizer often transforms subqueries into joins internally.

5. Scalar Subquery

·        Returns exactly one value

·        Can be used in SELECT clause
Example:

SELECT employee_id,

       (SELECT department_name

        FROM departments d

        WHERE d.department_id = e.department_id) AS dept_name

FROM employees e;

6. EXISTS Subquery

·        Checks if subquery returns at least one row

·        Stops scanning once first match found
Example:

SELECT *

FROM departments d

WHERE EXISTS (

    SELECT 1

    FROM employees e

    WHERE e.department_id = d.department_id

);

7. IN vs EXISTS

·        IN: Compares values, may be slower for large datasets

·        EXISTS: Checks presence, stops at first match, often faster for correlated queries

8. ANY and ALL

·        ANY: True if condition matches at least one value

WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 10);

·        ALL: True if condition matches all values

WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);

9. How Oracle executes subqueries

·        Non-correlated: Executed once, result passed to outer query

·        Correlated: Executed once per outer row, can be expensive
Optimizer may rewrite correlated subquery into join.

10. Inline View

·        Subquery in FROM clause acts like a temporary table
Example:

SELECT *

FROM (

    SELECT department_id, AVG(salary) avg_sal

    FROM employees

    GROUP BY department_id

) dept_avg

WHERE avg_sal > 5000;

11. Performance impact

Depends on:

·        Type (correlated vs non-correlated)

·        Index availability

·        Data volume

·        Optimizer transformations

Correlated subqueries without proper indexes can be slow.

12. Improving subquery performance

·        Use indexes on join columns

·        Replace correlated subquery with JOIN if possible

·        Use EXISTS instead of IN for large data

·        Avoid unnecessary nested subqueries

·        Ensure statistics are updated

13. Subquery in UPDATE

UPDATE employees e

SET salary = salary * 1.1

WHERE department_id IN (

    SELECT department_id

    FROM departments

    WHERE location_id = 1700

);

14. Subquery in DELETE

DELETE FROM employees

WHERE department_id NOT IN (

    SELECT department_id FROM departments

);

15. NOT IN problem with NULL

·        If subquery returns NULL, condition may return no rows

·        Safer approach: Use NOT EXISTS

16. Subquery vs Analytical Functions

Better using analytic function:

SELECT *

FROM (

    SELECT e.*, AVG(salary) OVER (PARTITION BY department_id) avg_sal

    FROM employees e

)

WHERE salary > avg_sal;

17. Common mistakes

·        Using correlated subqueries unnecessarily

·        Using NOT IN with NULL values

·        Forgetting indexes on correlated columns

·        Deeply nested subqueries reducing readability

·        Not checking execution plan

18. Real-world example

Find customers who placed orders:

SELECT *

FROM customers c

WHERE EXISTS (

    SELECT 1

    FROM orders o

    WHERE o.customer_id = c.customer_id

);

19. Interview Tip

“A subquery is a query nested inside another SQL statement. It can be single-row, multi-row, correlated, scalar, or inline view. Non-correlated subqueries execute once, while correlated subqueries execute per outer row. Oracle’s optimizer often transforms subqueries into joins for better performance.”

20. Best Practices Summary

·        Prefer EXISTS over IN for large datasets

·        Avoid NOT IN with NULL

·        Use joins when appropriate

·        Use indexes on correlated columns

·        Check execution plan

·        Keep queries readable and maintainable