How-to: Write a correlated subquery

Syntax

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
   Column1,
     ( SELECT column2 FROM table2 WHERE condition2 )
   Column3
FROM Table1
WHERE condition1

WHERE clause correlated subquery:

SELECT
   Column1,
   column2,
   Column3
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.

Examples

List customers along with their most recent order date:

SELECT custid, ordid, order_date
FROM orders O1
WHERE order_date = (SELECT
   Max(order_date)
   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

Related

INSERT
SELECT Statement.
SELECT Statement Aggregates.