In the previous examples, the subqueries were normal queries that you could run isolated from the outer query.
In some cases, you might need to write a subquery that depends on values from the outer query to retrieve its results. In these cases, you call the subquery a correlated subquery. This usually forces the execution of the subquery once per every row returned from the outer query.
You can easily identify a correlated subquery because it is enclosed in parentheses and it cannot be executed independently from the outer query.
Correlated subqueries can return a single scalar value, a list of values, or an array of values in the same way as standard subqueries.
Suppose you wanted to know the suggested UnitPrice