Non-Correlated Subqueries

A non-correlated subquery is a subquery that is independent of the outer query. Unlike a correlated subquery, which references columns from the outer query, a non-correlated subquery can be executed independently of the outer query because it does not rely on any columns from the outer query. It is evaluated once and can return a result for the entire outer query.

Non-correlated subqueries are commonly used to retrieve a single value or a set of values that the outer query needs to perform comparisons or filtering.

Key Characteristics of Non-Correlated Subqueries:

1.     Independent of the Outer Query:

o   A non-correlated subquery does not reference any columns from the outer query. It can be run independently and does not rely on data from the outer query to be evaluated.

2.     Evaluated Once:

o   Unlike a correlated subquery, which is executed for each row processed by the outer query, a non-correlated subquery is executed once and can return a single result or multiple values that the outer query uses in its filtering or comparison operations.

3.     Commonly Used for Filtering:

o   Non-correlated subqueries are often used in the WHERE or HAVING clauses for filtering based on a value or a set of values derived from the subquery.

4.     Performance:

o   Non-correlated subqueries tend to be more efficient than correlated subqueries because they are evaluated only once. They can return a single value (scalar subquery) or a set of values (e.g., list of values for IN or ANY).

 

Syntax of Non-Correlated Subqueries:

A basic non-correlated subquery can be structured as follows:

SELECT column1, column2, ...

FROM outer_table

WHERE columnX operator (SELECT columnY FROM inner_table WHERE condition);

In this example:

·        The outer query retrieves columns from outer_table.

·        The subquery (enclosed in parentheses) executes independently and can return a single value or multiple values, which are used in the outer query's condition.

 

Example of Non-Correlated Subquery:

Example 1: Finding employees whose salary is greater than the average salary.

SELECT employee_name, salary

FROM employees

WHERE salary > (

    SELECT AVG(salary) 

    FROM employees

);

Here:

·        The subquery calculates the average salary from the entire employees table.

·        The outer query then compares each employee's salary against the average salary calculated by the subquery.

The subquery is independent of the outer query and is evaluated once.

 

Types of Non-Correlated Subqueries:

1.     Scalar Subquery:

o   A scalar subquery is a non-correlated subquery that returns a single value. This value is then used by the outer query for comparisons.

Example:

SELECT employee_name

FROM employees

WHERE salary > (

    SELECT MAX(salary)

    FROM employees

    WHERE department_id = 10

);

In this case, the subquery returns the maximum salary for department 10, and the outer query uses that value to find employees who earn more than the maximum salary in department 10.

2.     Multi-Row Subquery:

o   A non-correlated subquery can return multiple values (e.g., using IN, ANY, or ALL), which the outer query can compare a column against.

Example:

SELECT employee_name

FROM employees

WHERE department_id IN (

    SELECT department_id

    FROM departments

    WHERE location_id = 1400

);

Here, the subquery retrieves all department_id values for departments located at location_id = 1400, and the outer query returns employees who work in those departments.

3.     Multi-Column Subquery:

o   A non-correlated subquery can return multiple columns as a result, and the outer query can compare these results in some way.

Example:

SELECT e.employee_name

FROM employees e

WHERE (e.salary, e.department_id) IN (

    SELECT MAX(salary), department_id

    FROM employees

    GROUP BY department_id

);

In this example, the subquery returns a set of salary and department combinations (maximum salary per department), and the outer query retrieves employees whose salary and department match the results of the subquery.

 

How Non-Correlated Subqueries Work:

1.     Outer Query Execution: The outer query processes each row and evaluates the condition in the WHERE or HAVING clause, which may contain the non-correlated subquery.

2.     Subquery Execution: The subquery is executed once to return a single value or a set of values.

3.     Result Comparison: The result of the subquery (a value or a set of values) is then used by the outer query to filter or compare against the rows being processed.

 

Common Use Cases for Non-Correlated Subqueries:

1.     Filter Results Based on Aggregate Values: Non-correlated subqueries are useful for filtering based on aggregate values such as average, sum, maximum, or minimum.

Example: Find employees who earn more than the average salary:

SELECT employee_name

FROM employees

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

);

2.     Filtering Based on Multiple Values: Use a non-correlated subquery with the IN operator when you need to filter rows based on a set of values.

Example: Find employees who work in departments located in a specific city:

SELECT employee_name

FROM employees

WHERE department_id IN (

    SELECT department_id

    FROM departments

    WHERE city = 'New York'

);

3.     Comparing with Subquery Results: Non-correlated subqueries can also be used with operators like =, >, <, BETWEEN, etc.

Example: Find products that are priced above the highest-priced product:

SELECT product_name, price

FROM products

WHERE price > (

    SELECT MAX(price)

    FROM products

);

4.     Find Data Based on Set Operations: Non-correlated subqueries can return results that can be used with IN, NOT IN, ANY, or ALL operators.

Example: Find employees who work in any department located in a specific region:

SELECT employee_name

FROM employees

WHERE department_id IN (

    SELECT department_id

    FROM departments

    WHERE region = 'North America'

);

 

Performance Considerations:

1.     Efficiency:

o   Non-correlated subqueries are more efficient than correlated subqueries because they are executed once and can be cached. They typically perform better, especially with large datasets.

2.     Indexes:

o   To optimize performance, ensure that columns used in the subquery, particularly those in the WHERE or JOIN clauses, are indexed.

o   Non-correlated subqueries benefit from indexing, especially when dealing with a large number of rows.

3.     Avoiding Nested Queries:

o   Non-correlated subqueries are typically more efficient than nested queries, which can sometimes require additional resources to execute multiple subqueries.

 

Correlated vs Non-Correlated Subqueries:

Aspect	Correlated Subquery	Non-Correlated Subquery
Dependency	Depends on the outer query for evaluation.	Independent of the outer query.
Execution	Executed once per row in the outer query.	Executed once for the entire outer query.
Performance	Can be slow, especially with large datasets.	Generally more efficient, since evaluated only once.
Common Use	Row-by-row comparison (e.g., salary comparison).	Single value or set of values comparison (e.g., max salary).
Example Use Case	Finding employees with a salary greater than the average salary in their department.	Finding employees with a salary greater than the average salary in the company.

 

Best Practices for Using Non-Correlated Subqueries:

1.     Limit the Results: Make sure the subquery returns a manageable number of rows. If it returns too many rows, it can slow down the outer query's performance.

2.     Use Indexes: Ensure proper indexing on columns that are referenced in the subquery, particularly those used for joins or filtering conditions.

3.     Avoid Complex Nesting: If you find yourself nesting too many subqueries, consider refactoring the query or using JOIN operations, which can often be more efficient.

4.     Use EXPLAIN PLAN: Analyze the execution plan for your query to understand the cost of the subquery and identify potential performance bottlenecks.