Union and Union all are used to combine the result from two tables. It means when you want data from 2 different tables or 2 different queries and wants to combine the result you can use Union and Union All. In SQL the Union and Union all operators are used to combine the results set into a single result set. But both operators have some key differences. First Create table and insert some data for query execution. Code: create table A(id int,name varchar(20)); create table B(id int,name varchar(20)); insert into A values(1,'A'),(2,'B'),(3,'C'),(4,'D'); insert into B values(1,'A'),(2,'B'),(3,'E'),(4,'F'); First, we consider some rule to use Union and Union all operators 1) A number of columns in both select queries should be the same. 2) In both queries, if columns in select query same then they must have the same datatype. 3) Whatever column name given by you in the first query is the final result set name. 4) If you want to apply group by and having clause then you need to apply this on individual select query means it is not applicable to a final result set. 5) Order By clause is applied to the final result set and if you try to apply for the order by clause before the last select statement you will get an error. Union This command is used to select the related information from two tables it is much like a join command. When a union operator combines a result set of 2 different queries then it selects only distinct records in newly generated resultset. Code: select * from A union select * from B; Union All This union all operator is the same as Union operator and it will select all records from 2 different select queries. If such a result set contains some common records then it will select these duplicate records also. Code: select * from A union all select * from B; Union and Union All difference: 1) Union will select only distinct records while the result set in union all contains duplicate records also. 2) Performance point of view union all is faster even though it fetch more records than union because the distinct sort is not applied so performance increase to a great extent.
