You can specify a scalar subquery as a column expression or parameterized value in the select list of a query. You can assign an alias to a scalar subquery defined in the select list, thus enabling the rest of the query to reference the subquery by that alias.
The following example specifies a scalar subquery (SELECT AVG(price)…) in its select list with an alias (avgprice) and then refers to it in the WHERE clause predicate (t2.price < avgprice).
SELECT category, title,(SELECT AVG(price) FROM movie_titles AS t1 WHERE t1.category=t2.category) AS avgprice FROM movie_titles AS t2 WHERE t2.price < avgprice;
For additional examples of specifying scalar subqueries in SELECT statements, see: