The Inner Query Returns One Value

A scalar subquery returns one row and one column and can be used like any other value in an expression. A subquery returning average price can filter products above that average.