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 ;




11 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. Dentistry Research Paper Writing Services have come up with Dentistry Writing Services for dentistry coursework writing service students in order for them to score straight A’s in their dentistry paper writing services.

    ReplyDelete
  4. 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
  5. Do you need Personal Finance?
    Business Cash Finance?
    Unsecured Finance
    Fast and Simple Finance?
    Quick Application Process?
    Finance. Services Rendered include,
    *Debt Consolidation Finance
    *Business Finance Services
    *Personal Finance services Help
    contact us today and get the best lending service
    personal cash business cash just email us below
    Contact Us: financialserviceoffer876@gmail.com
    call or add us on what's app +918929509036

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

    ReplyDelete
  7. Assessments are an essential part of the education system. If you are looking for top-notch assessment item development services assessment item development services, Acadecraft is right. We have a team of experienced enthusiasts who ensure to deliver the best services at cost-effective rates. We are available 24x7! Connect with us to know more.

    ReplyDelete
  8. 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