Stupid-Simple Union and Union All 2019

Sql Server By Sagar Jaybhay

Previous Articles : https://sagarjaybhay.com/category/sql-server/

All about Union and Union All

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.

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.

select * from A
union 
select * from B;

Union Query In SQL server 2019
Union Query In SQL server 2019

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.

select * from A
union all
select * from B;

Union All in SQL Server 2019
Union All in SQL Server 2019

Union and Union All Difference:

  1. In 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.

Union and Union All Query Execution Plan
Union and Union All Query Execution Plan

Joins In SQL :- https://sagarjaybhay.com/sql-join-inner-left-right-outer-self-join-2019/

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts