Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Subqueries Returning a Single Value

A subquery introduced with an unmodified comparison operator (a comparison operator not followed by ANY or ALL) must resolve to a single value. (If it doesn't, you'll get an error message, and the query won't be processed.) These subqueries take this general form:

					Start of SELECT, INSERT, UPDATE, DELETE statement; or
					subquery
WHERE expression comparison_operator
     (subquery)
[End of SELECT, INSERT, UPDATE, DELETE statement; or
					subquery]
				

Ideally, in order to use this kind of subquery, you must be familiar enough with your data and with the nature of the problem to know that the subquery will return exactly one value. When you expect more than one value, use IN or a modified comparison operator. Why not use IN ...