UNION Vs UNION ALL in SQL Server.

UNION:


The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2

UNION ALL:

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

SELECT UserName, Location FROM UserInfo UNION SELECT UserName, Location FROM UserDetails

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

Union	Union All
1.UNION only selects distinct values	1. UNION ALL selects all values (including duplicates).
2.Output is in sorted order	2. Output is not in sorted order