There are a couple of operators present in SQL Server. Union is one of them. It is used to combine the result set of two or more select statements. There are a couple of points that are important to keep in mind while using Union. The Columns must have similar data types. Every select statement within Union must have the same number of columns. We cannot allow duplicate values in Union. It removes duplicate records, hence it is slower as compared to Union All. Sometimes we select data from multiple tables and combine result sets of all select statements. That's the purpose of using Union Operator.
Union All combines results generated by multiple SQL queries or tables and it returns results into a single result set. Like in Union we cannot allow duplicate values but in Union All we can allow duplicate values. It does not remove duplicate records hence it is faster than Union. Union vs Union ALL in SQL.
The union all is another SQL command to perform set operations. Similar to Union, this will also combine the results of two or more select statements. It is also necessary to have the same number of columns and the same data types as the tables to which the union of all operations applies. Refer to the below two tables.
Union is an SQL command that combines the results of two or more select statements without returning any duplicate rows. Union All is an SQL command that combines the result of two or more select statements including duplicate rows. This is the key
difference between union and union all in SQL server. In other words, the union gives the resulting dataset without duplicate rows. On the other hand, union all gives the resulting dataset with duplicate rows.
