Using Union and order by clause in MySQL - MySQL Developer Tutorial

Using Union and order by clause in MySQL

Union or Union all is used to combine two or more result sets. We often need to return the output in sorted form after combining the results. There are different ways to return the sorted output or use order by. In below example, I am going to use Common table expressions.

Syntax:

with cte as (
Select column1,column2,column2 from TableOne
Union all
Select column1,column2,column3 from TableTwo
)
Select * from cte order by column1

Example : 

Let's say If I have to tables customer and customer4 , My query for Union and order by looks like below. I want to order by customerid.

Create customer table with sample data : 

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 customer4 table with some sample data :

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');

Using Union and order by clause in MySQL

with cte as (
Select customerid, firstname, lastname from customer
Union 
Select customerid, firstname, lastname from customer4)
Select * From cte order by customerid ;