In SQL, a co-related subquery refers to a subquery whose functionality depends on the output of the previous query.
The correlated subquery is run once on each row processed by the outer query.
Syntax
A sample syntax for a correlated subquery in SQL is as shown:
FROM table1 OUTER
WHERE column1 operator
(SELECT column1, column2
FROM table2
WHERE expr1 = OUTER.expr2);
Note: The parent query can be a select, delete, or update statement.
SQL Correlated Subquery – Example
Consider the table containing product information as shown below:
We can use a correlated subquery to find the product with a quantity equal to the maximum quantity in the table.
We can use a correlated subquery to find the product with a quantity equal to the maximum quantity in the table.
FROM products tb1
WHERE quantity IN (
SELECT MAX(tb2.quantity)
FROM products tb2
WHERE tb2.product_category = tb1.product_category
GROUP BY tb2.product_category
)
ORDER BY
product_name;
The query above should return:
Each product is evaluated by the outer query and then passed to the inner query, which finds the max quantity in that category.
