Non-Correlated Subquery FAQS

1. What Is a Non-Correlated Subquery?

A Non-Correlated Subquery is a subquery that:

• Does not reference any column from the outer query
• Can run independently
• Is usually executed once, and its result is reused by the outer query

Basic Structure

SELECT column_name

FROM table_name

WHERE column_name operator (

    SELECT column_name

    FROM table_name

);

The inner query does not depend on the outer query.

2. Why Is It Called “Non-Correlated”?

Because:

• There is no relationship (correlation) between inner and outer queries.
• The inner query does not use outer query aliases or columns.

If the inner query uses outer query columns → it becomes a correlated subquery.

3. How Does Oracle Execute a Non-Correlated Subquery?

Execution flow:

1.    Inner query runs first.

2.    Oracle stores the result.

3.    Outer query uses that result.

Example

SELECT employee_id, salary

FROM employees

WHERE salary > (

    SELECT AVG(salary) FROM employees

);

Execution:

• Step 1: Calculate AVG(salary)
• Step 2: Compare each employee salary with that value

Since the subquery doesn’t depend on outer rows → executed once.

4. Can Non-Correlated Subqueries Return Multiple Rows?

Yes.

They can be:

Single-row subquery

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

Multi-row subquery

WHERE department_id IN (

    SELECT department_id

    FROM departments

    WHERE location_id = 1700

);

Still non-correlated because inner query doesn’t reference outer table.

5. Where Can Non-Correlated Subqueries Be Used?

They can appear in:

• WHERE clause
• HAVING clause
• SELECT clause (scalar subquery)
• FROM clause (inline view)
• UPDATE statements
• INSERT statements
• DELETE statements

6. Non-Correlated Subquery in SELECT Clause

Called a scalar subquery (must return one value).

SELECT employee_id,

       (SELECT MAX(salary) FROM employees) AS max_salary

FROM employees;

The MAX(salary) is calculated once and displayed for every row.

7. Non-Correlated Subquery in FROM Clause (Inline View)

Also called an inline view.

SELECT *

FROM (

    SELECT department_id, AVG(salary) avg_sal

    FROM employees

    GROUP BY department_id

) dept_avg

WHERE avg_sal > 5000;

Here:

• Inner query creates a temporary result set.
• Outer query filters it.

8. Difference Between Non-Correlated and Correlated Subquery

Feature	Non-Correlated	Correlated
References outer query?	No	Yes
Execution	Once	Once per row
Performance	Usually faster	Can be slower
Dependency	Independent	Dependent

Correlated Example (for comparison)

SELECT e.employee_id

FROM employees e

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = e.department_id

);

This runs inner query for each employee.

9. Performance Characteristics

Efficient when:

• Subquery returns small result
• Uses aggregate functions
• Proper indexes exist
• Used as inline view for grouping

Possible Issues:

• Large subquery result sets
• Complex inline views
• Missing indexes

Oracle optimizer may transform subquery into JOIN internally.

10. Non-Correlated Subquery vs JOIN

Example using subquery:

SELECT employee_id

FROM employees

WHERE department_id IN (

    SELECT department_id

    FROM departments

    WHERE location_id = 1700

);

Equivalent JOIN:

SELECT e.employee_id

FROM employees e

JOIN departments d

ON e.department_id = d.department_id

WHERE d.location_id = 1700;

Both may produce same execution plan depending on optimizer.

11. What Happens If Subquery Returns No Rows?

If used with:

Single-row operator

WHERE salary > (SELECT salary FROM employees WHERE employee_id = 99999);

• Subquery returns NULL
• Condition becomes salary > NULL
• Result → No rows

IN operator

WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 9999);

• Returns no rows → outer query returns no rows

12. Can Non-Correlated Subquery Return Multiple Columns?

Yes, but only in certain contexts:

Tuple comparison

WHERE (department_id, job_id) IN (

    SELECT department_id, job_id

    FROM job_history

);

Inline view

FROM (SELECT col1, col2 FROM table_name)

13. Real-World Business Example

Requirement:

Find employees earning more than company average.

SELECT employee_id, salary

FROM employees

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

);

Why Non-Correlated?

• AVG(salary) is independent of outer query.
• Calculated once.

14. Interview Questions & Answers

Q1: What is a non-correlated subquery?

A subquery that does not reference columns from the outer query and executes independently.

Q2: How many times does it execute?

Usually once.

Q3: Is it always faster than correlated subquery?

Generally yes, but depends on data and indexing.

Q4: Can optimizer rewrite it?

Yes. Oracle may convert it into JOIN internally.

15. Common Mistakes

• Assuming subquery always slower than JOIN
• Forgetting NULL behavior
• Using = with multi-row results
• Ignoring execution plan
• Not indexing filtering columns

16. Best Practices

• Use aggregate functions when expecting single value
• Use IN for multiple results
• Check execution plan
• Use inline views for grouped filtering
• Add indexes on join/filter columns
• Avoid unnecessary nested subqueries

17. Quick Comparison Summary

Type	Execution	Example Use
Non-Correlated	Once	Global average comparison
Correlated	Per row	Department-based comparison

18. Short Interview Answer

“A non-correlated subquery is a subquery that does not reference the outer query and executes independently. It is typically executed once, and its result is used by the outer query. Oracle may internally rewrite it as a join for optimization.”