UNION and UNION ALL in T-SQL

UNION

The UNION operator is used to combine the result sets of two or more SELECT statements. To use it successfully, you must follow three key rules:

• Column Consistency: Each SELECT statement must have the same number of columns.

• Data Compatibility: The corresponding columns in each statement must have similar or compatible data types.

• Distinct Results: By default, UNION removes duplicate records and returns only distinct values.

UNION ALL

The UNION ALL operator works similarly to UNION, but with one major difference: it retrieves all rows from the selected tables without filtering for duplicates. Because it doesn't spend resources searching for and removing identical records, UNION ALL is significantly faster and more efficient when you know your data is unique or when you specifically want to include every result.  

Use UNION ALL by default if your data is already unique or if you don't mind including duplicate records. Because UNION must perform extra work to ensure only distinct values are returned, it is slower than UNION ALL. Only reach for the standard UNION when you specifically need to filter out overlapping data between your query results.