Union ALL in MySQL - MySQL Developer Tutorial

Union ALL in MySQL 

MySQL Union ALL operation is used to combine two or more result sets. Before you use UNION ALL operator in MySQL or MariaDB, you need to remember couple of things.

UNION ALL will return Duplicate records if result sets have same records.

1. When you combine two or more result sets, all the result sets should have the same number of columns.
2. The data type of the columns of each result set should be compatible or same like other results set columns.

Syntax : 

Union ALL in MySQL syntax

Select Column1,Column2,Column2 from TableOne
UNION ALL
Select Column1,Column2,Column3 from TableTwo
UNION ALL
Select Column1,Column2,Column2 from TableThree

Example : 

Let's create couple of tables  by using below scripts and then we use Union ALL operator.

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');

create table customer4.

CREATE TABLE `customer4` (
  `idcustomer` int,
  `firstname` char(50)  NULL,
  `lastname` Nchar(30)  NULL,
  `age` int(3) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer4(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(5,'Aamir','Naz',39,'505-4141969','1980-01-01','M')

Let  UNION ALL combine the rows or customer and customer4 table.

select * from customer
UNION ALL 
Select * from customer4;

MySQL Union ALL operator

Notice that in customer and customer4 some of the columns have different data types but it worked in UNION ALL as the data types are compatible.  Also duplicate records are returned as UNION ALL returns all records from each result set and does not remove duplicate.