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 ;




1 comment:

  1. MySQL triggers are keep programs dead mechanically to retort to specific events related to a table like associate degree insert, update or delete. This section shows you ways to figure with MySQL triggers effectively.n this section, we have a tendency to show you ways to use MySQL full-text search with varied full-text looking out techniques like linguistic communication search, mathematician language search and question growth.Aggregate functions permit you to perform a calculation on a collection of records and come back one worth. during this tutorial, you'll learn varied MySQL combination functions together with add, AVG, MAX, MIN and COUNT functions.

    Regards,
    Cheap Essay Writing Service

    ReplyDelete