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.”