UNION ALL command is equal to UNION command, except that UNION ALL selects all the values. The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all the rows from all the tables fitting your query specifics and combines them into a table.
UNION
The UNION command is used to select related information from two tables, much like the JOIN command. With UNION, only distinct values are selected by default. A UNION statement effectively does a SELECT DISTINCT on the result set.
UNION Statement in SQL Server
The UNION operator is used to combine the result set of two or more SELECT statements.
Here are some of the simple rules of using UNION in SQL:
Each SELECT statement within the UNION must have the same number of columns and the columns must have similar or compatible data types.
The columns in each SELECT statement must be in the same order.
If the columns sizes of the two tables vary, then while returning data, SQL server uses the larger of the two columns. Thus if a SELECT….UNION statement has a CHAR (5) and CHAR (10) column, then it will display output data of both the columns as a CHAR (10) column.
If the columns across the table have different column names then in general, the column names of the first query are used.
SQL UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;UNION ALL
The UNION ALL command is similar to the UNION command, except that UNION ALL selects all values. So with UNION ALL duplicate rows are not eliminated, rather they are included. This operator just pulls all rows from all tables which satisfy the query and combines them into a table. If you are sure that all the records returned from a UNION operation are unique, then using UNION ALL is a better option as it gives faster results. The results from a UNION ALL are unsorted. So after getting the results, you can sort them by using the ORDER BY clause. A ORDER BY should be inserted with the last SELECT statement.
SQL UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;Example:
DECLARE @Table1 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))
DECLARE @Table2 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))
INSERT INTO @Table1 VALUES(1,'Tejas', '88996655')
INSERT INTO @Table1 VALUES(2,'Jignesh', '99986655')
INSERT INTO @Table2 VALUES(1,'Rakesh', '88446655')
INSERT INTO @Table2 VALUES(2,'Purvi', '99986655')
SELECT * FROM @Table1
UNION
SELECT * FROM @Table2 
