Non-Correlated Subqueries FAQS

1. What is a Non-Correlated Subquery?

A non-correlated subquery is a subquery that does not reference any columns from the outer query. It can be executed independently of the outer query and its result is the same for all rows of the outer query.

2. What is the difference between a Correlated and Non-Correlated Subquery?

• Correlated Subquery: References columns from the outer query and is evaluated for each row of the outer query.
• Non-Correlated Subquery: Does not reference columns from the outer query and is executed only once, producing a single result that applies to all rows.

3. Can a Non-Correlated Subquery return multiple rows?

Yes, a non-correlated subquery can return multiple rows, especially when used with operators like IN, EXISTS, or when the subquery is expected to return a list of values.

4. How does Oracle execute a Non-Correlated Subquery?

Oracle executes a non-correlated subquery once, and then the result is applied to the outer query. This contrasts with correlated subqueries, which are evaluated once for each row of the outer query.

5. When should I use a Non-Correlated Subquery?

Use a non-correlated subquery when:

• You need to retrieve a value or set of values that do not depend on the outer query.
• You want to perform operations like filtering, aggregation, or comparison without relying on the outer query's row-specific values.

6. What are some common use cases for Non-Correlated Subqueries?

• In the SELECT clause: To get a value that is the same for every row.

·        SELECT employee_id, (SELECT MAX(salary) FROM employees) AS max_salary FROM employees;

• In the WHERE clause: To filter rows based on a condition computed by the subquery.

·        SELECT employee_id, name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

• In the IN clause: To compare a value against a list of values.

·        SELECT employee_id, name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1000);

7. What are the performance implications of Non-Correlated Subqueries?

Non-correlated subqueries tend to be faster than correlated subqueries because they are only executed once, regardless of the number of rows in the outer query. Oracle can also optimize non-correlated subqueries by caching their result.

8. Can Non-Correlated Subqueries be used in the HAVING clause?

Yes, non-correlated subqueries can be used in the HAVING clause to filter the results based on aggregated values.

SELECT department_id, COUNT(employee_id) AS num_employees

FROM employees

GROUP BY department_id

HAVING COUNT(employee_id) > (SELECT AVG(num_employees) FROM (SELECT department_id, COUNT(employee_id) AS num_employees FROM employees GROUP BY department_id));

9. What is an example of using a Non-Correlated Subquery in the FROM clause?

A non-correlated subquery can also be used in the FROM clause as a derived table.

SELECT dept_avg.salary, dept_avg.department_id

FROM (SELECT AVG(salary) AS salary, department_id FROM employees GROUP BY department_id) dept_avg

WHERE dept_avg.salary > 50000;

10. What happens if a Non-Correlated Subquery returns more than one row in a context where only one row is expected?

If a non-correlated subquery returns multiple rows where only a single value is expected, Oracle will throw an error. For example, if a subquery is used in a comparison operator like =, it must return only one row. If it returns multiple rows, the query will fail with an error like:

ORA-01427: single-row subquery returns more than one row

11. Can I use a Non-Correlated Subquery in the UPDATE statement?

Yes, you can use a non-correlated subquery in the UPDATE statement to assign a value based on a subquery result. For example:

UPDATE employees

SET salary = (SELECT MAX(salary) FROM employees WHERE department_id = 10)

WHERE department_id = 20;

12. Can a Non-Correlated Subquery be used in an EXISTS condition?

Typically, EXISTS is used with correlated subqueries, as it checks for the existence of rows based on the outer query. A non-correlated subquery doesn't make sense in EXISTS because it doesn't reference the outer query. Instead, you would use IN or ANY/ALL for non-correlated subqueries.

13. How do you optimize Non-Correlated Subqueries in Oracle?

• Indexing: Ensure relevant columns (e.g., those used in WHERE or JOIN conditions) are indexed.
• Materialized Views: If the subquery is complex or used frequently, consider using a materialized view to store the result of the subquery for faster retrieval.
• Query Rewriting: In some cases, restructuring the query to avoid unnecessary subqueries can improve performance.