Results 1 to 9 of 9

Thread: Difference between union and union all

  1. #1
    Junior Member
    Join Date
    Jun 2008
    Answers
    1

    Difference between union and union all

    What is the difference between UNION and UNION ALL? Which one is preferred and why ?


  2. #2
    Contributing Member
    Join Date
    May 2008
    Answers
    39

    Re: Difference between union and union all

    Hi RSP,

    UNION is used to select distinct values from two tables.

    UNION ALL is used to select all values (including duplicates) from the tables.

    Regards,
    Karthi


  3. #3
    Junior Member
    Join Date
    Apr 2008
    Answers
    4

    Re: Difference between union and union all

    Quote Originally Posted by rsp2709 View Post
    What is the difference between UNION and UNION ALL? Which one is preferred and why ?
    Union selects only unique row (data) from all queries.
    Union all selects all rows from all queries.

    Example.

    table1 has row (1,'Test')
    table2 has row (1,'Test')

    Union gives output
    1,'Test'

    Union all gives output
    1,'Test'
    1,'Test'


  4. #4
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Difference between union and union all

    UNION removes duplicate rows from multiple queries.

    UNION ALL shows all the rows from multiple queries.


  5. #5
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Difference between union and union all

    As UNION removes duplicate rows, there is a sort involved, adding a
    potentially big overhead to the process.


  6. #6
    Contributing Member
    Join Date
    Apr 2008
    Answers
    31

    Re: Difference between union and union all

    Hi RSP,

    As in the above discussions

    UNION is used to fetch the data from the two select queries with no duplication of data, where as UNION ALL fetches the data including the duplication.


  7. #7
    Expert Member
    Join Date
    Nov 2008
    Answers
    300

    Re: Difference between union and union all

    UNION and UNION ALL are SET operators that are used for two different queries.

    UNION will fetch the DISTINCT set of records returned by both the queries

    UNION ALL will fetch all the records returned by both the queries.

    The concept of UNION is similar to mathematics UNION Venn Diagrams (A U B)

    Eg:

    Code:
    select * from emp
    UNION
    select * from emp1
    /
    
    select * from emp
    UNION ALL
    select * from emp1
    
    
    In the above queries the number of columns and its datatypes should be same



  8. #8
    Junior Member
    Join Date
    May 2011
    Answers
    1

    Re: Difference between union and union all

    union all is faster than union, as union involve sorting operation to find distinct records.


  9. #9
    Junior Member
    Join Date
    Oct 2010
    Answers
    1

    Re: Difference between union and union all

    Very informative thread, this is one of popular interview question on SQL.


  10.  Sponsored Ads
    Ad


     

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact