Correlated subqueries

A correlated subquery is a type of subquery where the inner query depends on the outer query for its values. This means that the inner query is executed once for every row processed by the outer query. The inner query is driven by the outer query, as opposed to a non-correlated subquery, which can be run independently of the outer query.

Here's an example of a correlated subquery:

SELECT c1.CustomerName
FROM Customers c1
WHERE 500 < (SELECT SUM(o.Amount)
             FROM Orders o
             WHERE o.CustomerID = c1.CustomerID);

In this example, for each row in the Customers table (processed as c1), the subquery calculates the total Amount of orders. The correlation comes from the condition o.CustomerID = c1.CustomerID, which causes the subquery to be re-executed for each customer.

The result of this query is the list of customers who have total order amounts greater than 500.

Correlated subqueries can be used in SELECT, UPDATE, INSERT, and DELETE statements.

Note that because a correlated subquery requires the inner query to be processed for each row returned by the outer query, it can be slower than a similar query using a JOIN, particularly for large data sets. It's important to consider performance when designing queries and database architecture.