In Oracle SQL, Non-Correlated Subqueries are subqueries that do not reference any columns from the outer query. This means that the subquery can be executed independently of the outer query, and its result is static for all rows of the outer query. These subqueries are also sometimes referred to as independent subqueries because they don’t depend on the outer query’s row context.
Here are some key points and details about Non-Correlated Subqueries:
1. Definition
A Non-Correlated Subquery is a subquery where:
- The inner query (subquery) is independent of the outer query.
- It does not reference or use any column from the outer query.
- The result of the subquery is the same for every row of the outer query.
2. Usage
Non-correlated subqueries can be used in different clauses such as:
- SELECT
- WHERE
- HAVING
- FROM (as a derived table)
3. Examples
- In a SELECT Clause: A non-correlated subquery can be used in the SELECT clause to retrieve a single value or a set of values.
SELECT employee_id, salary,
(SELECT MAX(salary) FROM employees) AS max_salary
FROM employees;
In this example, the subquery (SELECT MAX(salary) FROM employees) is independent and does not rely on any column from the outer employees table.
- In a WHERE Clause: A non-correlated subquery can be used in the WHERE clause to filter rows based on a condition involving values from the subquery.
SELECT employee_id, name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
In this case, the subquery (SELECT AVG(salary) FROM employees) computes the average salary for all employees and is not dependent on the outer query’s current row.
- In a HAVING Clause: You can use a non-correlated subquery in a HAVING clause to apply a condition 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));
4. Subquery Result Types
- Non-correlated subqueries typically return:
- Single Value: A scalar value (e.g., a single row and column).
- Multiple Values: A result set, if the query is designed to return more than one row or column.
5. Performance Considerations
Non-correlated subqueries are typically more efficient than correlated subqueries because the subquery can be executed just once. Its result does not need to be re-evaluated for every row of the outer query.
- Since the subquery does not depend on the outer query, it can be computed and stored once and then reused.
- This contrasts with correlated subqueries, which are evaluated for each row of the outer query.
6. Key Differences Between Correlated and Non-Correlated Subqueries
- Correlated Subquery: References columns from the outer query. It is evaluated once for each row of the outer query.
- Non-Correlated Subquery: Does not reference columns from the outer query and is executed once for the entire outer query.
7. Optimizing Non-Correlated Subqueries
- Since the non-correlated subquery is executed once, Oracle may optimize these queries by caching the result, especially if the subquery is used multiple times in the outer query.
- Oracle's optimizer may decide to execute the subquery first and then apply it to the outer query’s result.
8. Example with IN Operator:
Another common use of a non-correlated subquery is in the IN clause, where you check if a value exists in a set of values returned by a subquery.
SELECT employee_id, name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1000);
Here, the subquery (SELECT department_id FROM departments WHERE location_id = 1000) does not depend on the outer query, so it's a non-correlated subquery.
9. Advantages of Non-Correlated Subqueries
- Simplicity: Easier to write and understand as they do not involve the outer query.
- Performance: Typically faster than correlated subqueries since the subquery is evaluated once.
No comments:
Post a Comment