INF: Hash Hint Is Ignored When a UNION Operator Follows an IN Operator (274075)

MORE INFORMATION

The execution plan for the following query shows concatenation instead of hash. The hash union hint in the option clause is ignored. Because the UNION is part of an IN predicate, duplicates are irrelevant. To ignore duplicates, the UNION operator is converted into a UNION ALL operator. The UNION ALL is implemented only as CONCAT. The hash operation is only needed if duplicates must be removed. The UNION operator specifies that multiple result sets are to be combined and returned as a single result set. The UNION ALL operator includes all rows in the results, including duplicates. If ALL is not specified, the duplicate rows are removed. Because duplicates are allowed, there is no need to use a hash operation.

Select au_id from authors where au_id IN
(

    ( SELECT titleauthor.au_id
       FROM  titleauthor, sales 
       WHERE (titleauthor.title_id = sales.title_id)
         AND (sales.title_id ='BU1032')
    )

UNION
    ( SELECT C.au_id 
       FROM  authors  C
       WHERE (authors.zip = '84152')
         AND (authors.au_id = C.au_id)
    )
)
option(hash union)