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 ;




7 comments:

  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
  2. Hello, I would like to thank you for sharing this interesting information. I was actually looking for someone who will rewrite my article .

    ReplyDelete
  3. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. using an essay writing service

    ReplyDelete
  4. It implies the mail or message can be meant any language that is effortlessly perceived by the recipient Low spam scores

    ReplyDelete
  5. so if you are also among those Students who have Registered them Self as a Regular or Private Student are Suggested to Download these UP Board 10th Sample Paper 2023 must Prepare all Subjects. Model Papers 2023 are very helpful Material that is Provided by the official education Board. UP Board 10th Previous Question Paper 2023 are very important for the Preparation of Public Exam of UPMSP for the Students. UPMSP Madhyamik Model Paper 2023 10th Students of can easily get their Latest and most valuable Mock Test Paper 2023 from this website. Students only have to Download the UP Board 10th Mock Test Paper 2023 for all the important subjects are available Including the English, Hindi, Mathematics, Science, Social Science etc.

    ReplyDelete