You learned in “List Filtering with IN” in Chapter 4 how to use the IN keyword in a WHERE clause to compare a literal, column value, or expression to a list of values. You also can use a subquery to generate the list. The important characteristics of a subquery set membership test are:
IN works the same way with the values in a subquery result as it does with a parenthesized list of values (see “List Filtering with IN” in Chapter 4).
The subquery can be simple or correlated (see “Simple and Correlated Subqueries” earlier in this chapter).
The subquery’s SELECT-clause list can include only one expression or column name.
The compared values must have the same data type or must be implicitly convertible to the same type ...