Learn T-SQL Querying

UNION ALL versus UNION

The UNION and UNION ALL operators are used to combine the results of two separate queries into a single result set. If it is possible for rows to be duplicated between the two queries and we do not want to return duplicate rows, using the UNION operator will cause SQL Server to filter out any duplicate rows in the two sets. Doing this requires a Sort operation, however, so it is important to only use UNION when necessary. If duplicate values are allowed in the final result set, or if the source results sets cannot have duplicates to begin with, for example, both input have unique constraints or primary keys and the sets don't overlap, using a UNION ALL is more efficient. This avoids introducing implicit sort operations ...