All about Union and Union All

Discussion in 'SQL Server' started by Sagar Jaybhay, Dec 29, 2019.

  1. Sagar Jaybhay

    Sagar Jaybhay New Member

    Joined:
    Jan 28, 2019
    Messages:
    29
    Likes Received:
    17
    Trophy Points:
    3
    Gender:
    Male
    Occupation:
    Sr. Software Developer
    Location:
    Pune
    Home Page:
    https://sagarjaybhay.net
    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 Query In SQL server 2019.png


    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 All in SQL Server 2019.png


    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.

    Union Query Execution Plan.png
     
    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice