Example: Scalar Subquery in the Select List of a SELECT Statement - Teradata VantageCloud Lake

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 refers to the alias 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:

• Example: Scalar Subquery in the WHERE Clause of a SELECT Statement
• Example: SELECT Statement with a Scalar Subquery in the GROUP BY Clause
• Example: Scalar Subquery in the WITH … BY Clause of a SELECT Statement
• Example: Scalar Subquery in the ON Clause of a Left Outer Join