A subquery is a query that is nested inside another query, usually enclosed in parentheses. It can be either a correlated or a non-correlated subquery.
A non-correlated subquery is a subquery that can be executed independently of the outer query. It is evaluated only once and the result is used as a fixed value for the outer query. In other words, the subquery is not related to the outer query, and it can be executed on its own. Here’s an example:
SELECT name
FROM customers
WHERE age > (SELECT AVG(age) FROM customers);The subquery ‘(SELECT AVG(age) FROM customers)‘ returns a single value, which is then used to filter the results of the outer query. This subquery is not correlated to the outer query, which means it can be executed independently of it.
On the other hand, a correlated subquery is a subquery that is related to the outer query. In other words, the subquery is executed once for each row of the outer query. The result of the subquery depends on the values of the outer query. Here’s an example:
SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
FROM customers;In this query, the subquery ‘(SELECT COUNT(*) FROM orders WHERE customer_id = customers.id)‘ depends on the value of ‘customers.id‘ in the outer query. For each row in the outer query, the subquery is executed with a different value of ‘customers.id‘. This is called correlation. It means that the subquery is executed once for each row of the outer query, which can make correlated subqueries slower than non-correlated subqueries.
To summarize, the main difference between correlated and non-correlated subqueries is whether or not they are related to the outer query. Non-correlated subqueries can be evaluated independently of the outer query, while correlated subqueries are executed once for each row of the outer query.