How-to: Write a correlated subquery


A correlated subquery can be added to either the SELECT clause of a query, adding an additional calculated value to the result set, or it can be added to the WHERE condition, adding an additional calculated restriction to the result set.

SELECT clause correlated subquery:

     ( SELECT column2 FROM table2 WHERE condition2 )
FROM Table1
WHERE condition1

WHERE clause correlated subquery:

FROM Table1
WHERE condition1,
   condition3 ( SELECT column2 FROM table2 WHERE condition4 )

In all cases you will want the subquery to return a single value which can be done in two ways:

Select an item from a table and restrict the result to a single value via its primary key:
Select Product_name from products where product_id = 64;

Add an aggregate function so that no matter how many items are in the table only a single value will be returned:
Select Avg(product_price) from products;

It is also possible to add an aggregate clause such as Round() to the value returned by a subquery, just as if it were any other column result.


List customers along with their most recent order date:

SELECT custid, ordid, order_date
FROM orders O1
WHERE order_date = (SELECT
   FROM orders O2
   WHERE O2.custid = O1.custid)

Question: what happens if a customer has placed 2 orders on the same day?

“Correlation and causation are two quite different words, and the innumerate are more prone to mistake them than most” ~ John Allen Paulos


SELECT Statement.
SELECT Statement Aggregates.

Copyright © 1999-2024
Some rights reserved