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