Unions in Subqueries

A union clause is allowed in a subquery inside a derived-table expression.

The following example uses a union clause in a subquery within a SQL-derived table to list the titles of books sold at stores listed in the sales and sales_east tables:
select title_id from salesdetail
   where stor_id in
      (select stor_id from
         (select stor_id from sales
          union
          select stor_id from sales_east)
       dt_stores)