Subquery Best Practices FAQS

1. What Are Oracle Subqueries?

A subquery is a query nested inside another query. Types include:

• Single-row subquery: Returns one row → used with =, <, >
• Multi-row subquery: Returns multiple rows → used with IN, ANY, ALL
• Scalar subquery: Returns a single value → used in SELECT or WHERE
• Correlated subquery: References outer query columns → executed per row
• Non-correlated subquery: Independent → executed once
• Inline view: Subquery used in the FROM clause as a derived table

2. Why Follow Best Practices?

• Avoid performance issues (especially with correlated subqueries)
• Ensure correct results (avoid NULL pitfalls, ORA-01427 errors)
• Improve readability and maintainability
• Help the optimizer generate faster execution plans

3. Best Practice 1: Prefer Non-Correlated Subqueries

• Executed once → faster
• Correlated subqueries execute per row → slower for large datasets

Example:

SELECT e.employee_id

FROM employees e

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = e.department_id

);

Better (Analytic Function):

SELECT employee_id

FROM (

    SELECT employee_id, salary,

           AVG(salary) OVER (PARTITION BY department_id) dept_avg

    FROM employees

)

WHERE salary > dept_avg;

4. Best Practice 2: Use Aggregates for Single-Row Subqueries

• Ensures exactly one row is returned
• Prevents ORA-01427 (too many rows error)

Example:

SELECT employee_id

FROM employees

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

5. Best Practice 3: Prefer EXISTS Over IN (Correlated Subqueries)

• EXISTS stops processing once a match is found
• More efficient and safer with NULL values

Example:

SELECT e.employee_id

FROM employees e

WHERE EXISTS (

    SELECT 1

    FROM job_history j

    WHERE j.employee_id = e.employee_id

);

6. Best Practice 4: Avoid NOT IN with NULLs

• NOT IN fails if subquery returns NULL
• Use NOT EXISTS instead

Example:

SELECT e.employee_id

FROM employees e

WHERE NOT EXISTS (

    SELECT 1

    FROM departments d

    WHERE e.department_id = d.department_id

);

7. Best Practice 5: Use Inline Views or WITH Clause

• Improves readability
• Avoids repeated calculations
• Helps optimizer

Inline View:

SELECT e.employee_id, d.avg_sal

FROM employees e

JOIN (

    SELECT department_id, AVG(salary) AS avg_sal

    FROM employees

    GROUP BY department_id

) d

ON e.department_id = d.department_id

WHERE e.salary > d.avg_sal;

CTE (WITH Clause):

WITH dept_avg AS (

    SELECT department_id, AVG(salary) AS avg_sal

    FROM employees

    GROUP BY department_id

)

SELECT e.employee_id, e.salary

FROM employees e

JOIN dept_avg d

ON e.department_id = d.department_id

WHERE e.salary > d.avg_sal;

8. Best Practice 6: Index Correlated Columns

• Improves performance for repeated lookups

Example:

CREATE INDEX idx_emp_dept ON employees(department_id);

9. Best Practice 7: Use Analytic Functions

• Avoid repeated subquery execution
• Efficient for grouped calculations

Example:

SELECT employee_id, salary,

       AVG(salary) OVER (PARTITION BY department_id) dept_avg

FROM employees

WHERE salary > dept_avg;

10. Best Practice 8: Limit Rows Early

• Apply filters inside subqueries to reduce workload

Example:

SELECT employee_id

FROM employees

WHERE department_id IN (

    SELECT department_id

    FROM departments

    WHERE location_id = 1700

);

11. Best Practice 9: Avoid Deeply Nested Subqueries

• Hard to read and maintain
• Difficult for optimizer
• Prefer joins, inline views, or CTEs

12. Best Practice 10: Check Execution Plan

• Validate query performance
• Identify inefficiencies like full table scans

Example:

EXPLAIN PLAN FOR

SELECT e.employee_id

FROM employees e

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

);

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

13. Summary Table

Best Practice	Description
Prefer Non-Correlated	Executes once, faster
Use Aggregates	Prevent ORA-01427
Prefer EXISTS over IN	Better for correlated queries
Avoid NOT IN with NULLs	Use NOT EXISTS
Inline Views / CTEs	Simplify complex queries
Index Correlated Columns	Improves performance
Use Analytic Functions	Efficient group computations
Limit Rows Early	Reduces unnecessary processing
Avoid Deep Nesting	Improves readability & optimization
Check Execution Plan	Ensures optimal performance

14. Short Interview Answer

“Oracle subquery best practices include using non-correlated subqueries when possible, preferring EXISTS over IN for correlated queries, using aggregates to guarantee single-row results, indexing correlated columns, limiting rows early, using inline views or CTEs for complex aggregations, and leveraging analytic functions to replace repeated correlated subqueries. Always check the execution plan to ensure optimal performance.”