How to use union and order by clause in MySQL?

Union is a type of operator in MySQL. We can use ORDER BY with this to filter records. Use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set.

Let us see an example.

Creating first table

mysql> create table UnionDemo1
   -> (
   -> id int
   -> );
Query OK, 0 rows affected (0.59 sec)

Inserting records into first table.

mysql> insert into UnionDemo1 values(1),(4),(10);
Query OK, 3 rows affected (0.21 sec)
Records: 3  Duplicates: 0  Warnings: 0

To display all records.

mysql> select *from UnionDemo1;

The following is the output that displays all the records.

+------+
| id   |
+------+
|    1 |
|    4 |
|   10 |
+------+
3 rows in set (0.00 sec)

Let us create second table.

mysql> create table UnionDemo2
   -> (
   -> id int
   -> );
Query OK, 0 rows affected (0.54 sec)

Inserting records into second table.

mysql> insert into UnionDemo2 values(2),(3),(5),(6),(7),(8),(9);
Query OK, 7 rows affected (0.21 sec)
Records: 7  Duplicates: 0  Warnings: 0

To display all the records.

mysql> select *from UnionDemo2;

Here is the output.

+------+
| id   |
+------+
|    2 |
|    3 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
7 rows in set (0.00 sec)

Let us now see the query to apply UNION with Order by.

mysql> select id from UnionDemo1
   -> union
   -> select id from UnionDemo2
   -> order by id desc;

The following is the output.

+------+
| id   |
+------+
|   10 |
|    9 |
|    8 |
|    7 |
|    6 |
|    5 |
|    4 |
|    3 |
|    2 |
|    1 |
+------+
10 rows in set (0.05 sec)

George John

Updated on: 30-Jul-2019

5K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started