Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Difference between UNION and UNION ALL operators in MySQL.

    • 0
    • 1
    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 500
    Comment on it

    In MySQL UNION operator used to combine the result of multiple SELECT statements. When we apply the UNION operator then it selects only distinct values by default (No duplicate values are allowed).

    UNION Operator

    When we apply UNION operator to SELECT statements then each SELECT statement must have the same number of columns, and the columns must also have similar data types.And the order of columns must be in same in SELECT statements.

    UNION Operator Syntax

    SELECT column_names FROM table1
    UNION
    SELECT column_names FROM table2;
    

    UNION ALL Operator

    UNION ALL Operator is also used to combine the result of multiple SELECT statements but it allows the duplicate values.

    UNION ALL Syntax

    SELECT column_name FROM table1
    UNION ALL
    SELECT column_name FROM table2;
    

    Example:

    We have two tables with the following values.

     user
    
    id  user_name     country   
    .......................................
    1   John           Canada     
    2   Chris          America        
    3   Joy            London         
    4   Jenny          Korea                              
    
        student
    
    id  student_name     country   
    .......................................
    1   Rony                USA     
    2   Bonney           USA                
    3   Kat              Mexico 
    

    UNION Example

    The following statement selects all the different distinct countries only from the "user" and the "student" tables:

    Example

    SELECT country FROM user
    UNION
    SELECT country FROM students
    ORDER BY country;
    

    Result

    user
    
    country   
    .......................................
    Canada     
    America        
    London         
    Korea     
    USA                
    Mexico
    

    UNION ALL Example

    The following statement will select all countries including duplicate values from the "user" and "student" tables:

    Example

    SELECT country FROM user
    UNION ALL
    SELECT country FROM students
    ORDER BY country;
    

    Result

    user
    
    country   
    .......................................
    Canada     
    America        
    London         
    Korea     
    USA 
    USA               
    Mexico
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: