UNION, INTERSECT, and EXCEPT
UNION, INTERSECT, and EXCEPT combine results from multiple subselects.
UNION returns values from the first and second subselects.
INTERSECT returns values that are present in both the first and second subselects.
EXCEPT returns values from the first subselect that are absent from the second subselect.
UNION, INTERSECT, and EXCEPT return distinct results, such that there are no duplicates.
UNION ALL, INTERSECT ALL, and EXCEPT ALL return all applicable values, including duplicates. These queries are faster, because they do not compute distinct results.
You can improve the performance of a query by using covering indexes, where the index includes all the information needed to satisfy the query. For more information, see Covering Indexes.