MySql - Correlated vs non-correlated subqueries

Correlated vs Non-Correlated Subqueries in MySQL

Non-Correlated Subqueries

Definition

A non-correlated subquery is an independent query whose result is used by the outer query. It does not reference any columns from the outer query and can be executed on its own.

Characteristics

• Executed once for the entire outer query.
• Independent of the outer query.
• Can be run standalone.
• Generally faster due to single execution.

Syntax

SELECT column1, column2
FROM table1
WHERE column3 operator (
    SELECT column4
    FROM table2
    WHERE condition
);

Example

Retrieve employees who earn more than the average salary:

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

In this example, the subquery calculates the average salary independently, and the outer query retrieves employees earning above that average.

Correlated Subqueries

Definition

A correlated subquery is a subquery that references columns from the outer query. It is executed once for each row processed by the outer query.

Characteristics

• Executed multiple times, once for each row of the outer query.
• Dependent on the outer query.
• Cannot be run standalone.
• May have performance implications due to repeated execution.

Syntax

SELECT column1, column2
FROM table1 alias1
WHERE column3 operator (
    SELECT column4
    FROM table2 alias2
    WHERE alias2.column5 = alias1.column5
);

Example

Retrieve employees who earn more than the average salary in their department:

SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

Here, the subquery calculates the average salary for each department, and the outer query retrieves employees earning above their department's average.

Key Differences Between Correlated and Non-Correlated Subqueries

Use Cases

Non-Correlated Subquery Use Case

Retrieve products with a price higher than the average price:

SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Correlated Subquery Use Case

Retrieve customers who have placed more than one order:

SELECT customer_id, name
FROM customers c
WHERE (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) > 1;

Performance Considerations

Correlated subqueries can be less efficient due to their repeated execution for each row of the outer query. In contrast, non-correlated subqueries are generally more efficient as they are executed only once. When dealing with large datasets, it's advisable to consider alternatives such as JOINs or Common Table Expressions (CTEs) to optimize performance.

Best Practices

• Use non-correlated subqueries when possible for better performance.
• Consider rewriting correlated subqueries using JOINs or CTEs.
• Always test and analyze query performance, especially with large datasets.
• Ensure indexes are in place on columns used in subqueries to enhance performance.

Understanding the differences between correlated and non-correlated subqueries is essential for writing efficient SQL queries. While correlated subqueries are useful for row-by-row comparisons, they can be performance-intensive. Non-correlated subqueries offer better performance and are suitable for aggregate calculations and filters. By choosing the appropriate subquery type and optimizing queries, you can enhance the performance and maintainability of your database operations.