Example HTML page

MySQL Union operator

In this post we are going to learn how the union statement works in MySQL.

Union

This operator is used to integrate the result set of two or more select queries. While executing the queries with union operator the following are the rules must be considered:

  • Each SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in each SELECT statement must also be in the same order.

For better understanding of ‘UNION’ let us check with general example:

U1={2,3,4}; U2={3,4,5,6}

Union of U1 and U2 is {2,3,4,5,6}

Syntax of UNION:

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

Ex

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| captcha            |
| mysql              |
| performance_schema |
| sampledb           |
| sports             |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> use sampledb;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_sampledb |
+--------------------+
| t1                 |
| t2                 |
+--------------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+-----------+
| elements1 |
+-----------+
|        10 |
|        15 |
|        20 |
|        25 |
+-----------+
4 rows in set (0.00 sec)

mysql> select * from t2;
+-----------+
| elements2 |
+-----------+
|        15 |
|        20 |
|        25 |
|        30 |
+-----------+
4 rows in set (0.00 sec)

Here we are integrating both the columns of tables t1 and t2 using UNION operator. The output will be as follows.

mysql> select * from t1 union select * from t2;
+-----------+
| elements1 |
+-----------+
|        10 |
|        15 |
|        20 |
|        25 |
|        30 |
+-----------+
5 rows in set (0.00 sec)

Union all

The union operator only allows distinct values by default whereas union all allows all duplicate values.

Let me describe ‘UNION ALL’ with general example:

S1={1,2,3}; S2={2,3}

Union All of S1 and S2 is {1,2,3,2,3}

Syntax:

select column_name(s) from table1 union all select column_name(s) from table2;

The working of union all is as follows:

mysql> select * from t1 union all select * from t2;
+-----------+
| elements1 |
+-----------+
|        10 |
|        15 |
|        20 |
|        25 |
|        15 |
|        20 |
|        25 |
|        30 |
+-----------+
8 rows in set (0.00 sec)

The above examples illustrates the working of union and union all when there is one column but what if there are multiple columns. Let’s see.

(Here I am changing the database where there will be the tables with multiple columns.)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| captcha            |
| mysql              |
| performance_schema |
| sampledb           |
| sports             |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> use sports;
Database changed
mysql> show tables;
+------------------+
| Tables_in_sports |
+------------------+
| players          |
| rankings         |
| rankings_1       |
| union_1          |
+------------------+
4 rows in set (0.00 sec)
mysql> select * from rankings;
+-----------+------+---------+
| name      | rank | country |
+-----------+------+---------+
| Dhoni     |   26 | India   |
| Sharapova |   16 | Russia  |
| Mike      |   13 | USA     |
+-----------+------+---------+
6 rows in set (0.00 sec)

mysql> select * from rankings_1;
+---------+------+-----------+
| name    | rank | country   |
+---------+------+-----------+
| Nadal   |    8 | Spain     |
| L Messi |   11 | Barcelona |
| C Marin |    3 | Spain     |
+---------+------+-----------+
3 rows in set (0.01 sec)

From the above list of four tables I am choosing rankings and rankings_1 tables. I am going to combine the columns of both the tables and the output will be as follows.

mysql> select * from rankings union select * from rankings_1;
+-----------+------+-----------+
| name      | rank | country   |
+-----------+------+-----------+
| Dhoni     |   26 | India     |
| Sharapova |   16 | Russia    |
| Mike      |   13 | USA       |
| Nadal     |    8 | Spain     |
| L Messi   |   11 | Barcelona |
| C Marin   |    3 | Spain     |
+-----------+------+-----------+
6 rows in set (0.00 sec)

Suppose if you want to integrate required columns i.e name and rank from rankings table and rank and country from rankings_1 table, then,

mysql> select name,rank from rankings union select rank,country from rankings_1;
+-----------+-----------+
| name      | rank      |
+-----------+-----------+
| Dhoni     | 26        |
| Sharapova | 16        |
| Mike      | 13        |
| 8         | Spain     |
| 11        | Barcelona |
| 3         | Spain     |
+-----------+-----------+
6 rows in set (0.02 sec)

mysql> select name,rank from rankings union select country from rankings_1;
ERROR 1222 (21000): The used SELECT statements have a different number of column
s

In the above query we are mentioning two columns for the first select statement but in the second select statement we are mentioning only one column. Since it is violating one of the rules you will be displayed an error.

Union all:

As we discussed earlier, union all allows displaying all the duplicate values in the table.

Ex:

mysql> select name,rank from rankings union all select rank,country from ranking
s_1;
+-----------+-----------+
| name      | rank      |
+-----------+-----------+
| Dhoni     | 26        |
| Sharapova | 16        |
| Mike      | 13        |
| 8         | Spain     |
| 11        | Barcelona |
| 3         | Spain     |
+-----------+-----------+
6 rows in set (0.00 sec)

In the above table there are no duplicate values.What happens if we have duplicate values!!!… Lets see.

(Before this I inserted two records into rankings table to show you what happens if the table contains duplicate values.). See the table.

mysql> select * from rankings;
+-----------+------+---------+
| name      | rank | country |
+-----------+------+---------+
| Dhoni     |   26 | India   |
| Sharapova |   16 | Russia  |
| Mike      |   13 | USA     |
| Dhoni     |   26 | India   |
| Mike      |   13 | USA     |
| Raina     |    6 | India   |
+-----------+------+---------+
6 rows in set (0.00 sec)

Now perform union operation on the above table.

mysql> select name,country from rankings union select name,rank from rankings_1;

+-----------+---------+
| name      | country |
+-----------+---------+
| Dhoni     | India   |
| Sharapova | Russia  |
| Mike      | USA     |
| Raina     | India   |
| Nadal     | 8       |
| L Messi   | 11      |
| C Marin   | 3       |
+-----------+---------+
7 rows in set (0.00 sec)

Here the duplicate values are not displayed in the output. Since union allows only distinct values.

Now perform union all, then,

mysql> select name,country from rankings union all select name,rank from ranking
s_1;
+-----------+---------+
| name      | country |
+-----------+---------+
| Dhoni     | India   |
| Sharapova | Russia  |
| Mike      | USA     |
| Dhoni     | India   |
| Mike      | USA     |
| Raina     | India   |
| Nadal     | 8       |
| L Messi   | 11      |
| C Marin   | 3       |
+-----------+---------+
9 rows in set (0.00 sec)

Here the duplicate values are also displayed in the output.

‘Union’ statement using ‘where’:

We can also perform union operation using ‘where’ clause.

mysql> select * from rankings where name="Dhoni" union select * from rankings_1
where country="India";
+-------+------+---------+
| name  | rank | country |
+-------+------+---------+
| Dhoni |   26 | India   |
+-------+------+---------+
1 row in set (0.00 sec)

‘Union all’ using ‘where’:

The following example describes union all using “where” clause.

mysql> select * from rankings where name="Dhoni" union all select * from ranking
s_1 where country="India";
+-------+------+---------+
| name  | rank | country |
+-------+------+---------+
| Dhoni |   26 | India   |
| Dhoni |   26 | India   |
+-------+------+---------+
2 rows in set (0.00 sec)

Click here 

To watch this tutorial post on YouTube Channel.

Example HTML page

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest