MySQL UNION

Last update on August 19 2022 21:50:41 (UTC/GMT +8 hours)

UNION

In MySQL, the UNION operator is used to combine the result from multiple SELECT statements into a single result set.

The default characteristic of UNION is, to remove the duplicate rows from the result. The DISTINCT keyword which is optional does not make any effect, because, by default, it specifies duplicate-row removal. But if we use the optional keyword ALL, the duplicate-row removal does not happen and the result set includes all matching rows from all the SELECT statements.

Syntax :

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

Pictorial presentation of UNION operator

The UNION operator returns result from both queries after eliminating the duplicate rows.

Sample Tables

employees:

job_history:

Example

If we want to display the present and previous details of jobs of all employees once the following MySQL statement can be used.

Select employee_id, job_id
FROM employees
UNION
Select employee_id,job_id
FROM job_history;

Sample Output:

+-------------+------------+
| employee_id | job_id     |
+-------------+------------+
|         100 | AD_PRES    | 
|         101 | AD_VP      | 
|         102 | AD_VP      | 
|         103 | IT_PROG    | 
|         104 | IT_PROG    | 
|         105 | IT_PROG    | 
|         106 | IT_PROG    | 
|         107 | IT_PROG    | 
|         108 | FI_MGR     | 
|         109 | FI_ACCOUNT | 
|         110 | FI_ACCOUNT | 
|         111 | FI_ACCOUNT | 
|         112 | FI_ACCOUNT | 
|         113 | FI_ACCOUNT | 
|         114 | PU_MAN     | 
|         115 | PU_CLERK   | 
|         116 | PU_CLERK   | 
|         117 | PU_CLERK   | 
|         118 | PU_CLERK   | 
|         119 | PU_CLERK   | 
|         120 | ST_MAN     | 
|         121 | ST_MAN     | 
|         122 | ST_MAN     | 
|         123 | ST_MAN     | 
|         124 | ST_MAN     | 
|         125 | ST_CLERK   | 
|         126 | ST_CLERK   | 
|         127 | ST_CLERK   | 
|         128 | ST_CLERK   | 
|         129 | ST_CLERK   | 
|         130 | ST_CLERK   | 
|         131 | ST_CLERK   | 
|         132 | ST_CLERK   | 
|         133 | ST_CLERK   | 
|         134 | ST_CLERK   | 
|         135 | ST_CLERK   | 
|         136 | ST_CLERK   | 
|         137 | ST_CLERK   | 
|         138 | ST_CLERK   | 
|         139 | ST_CLERK   | 
|         140 | ST_CLERK   | 
|         141 | ST_CLERK   | 
|         142 | ST_CLERK   | 
|         143 | ST_CLERK   | 
|         144 | ST_CLERK   | 
|         145 | SA_MAN     | 
|         146 | SA_MAN     | 
|         147 | SA_MAN     | 
|         148 | SA_MAN     | 
|         149 | SA_MAN     | 
|         150 | SA_REP     | 
|         151 | SA_REP     | 
|         152 | SA_REP     | 
|         153 | SA_REP     | 
|         154 | SA_REP     | 
|         155 | SA_REP     | 
|         156 | SA_REP     | 
|         157 | SA_REP     | 
|         158 | SA_REP     | 
|         159 | SA_REP     | 
|         160 | SA_REP     | 
|         161 | SA_REP     | 
|         162 | SA_REP     | 
|         163 | SA_REP     | 
|         164 | SA_REP     | 
|         165 | SA_REP     | 
|         166 | SA_REP     | 
|         167 | SA_REP     | 
|         168 | SA_REP     | 
|         169 | SA_REP     | 
|         170 | SA_REP     | 
|         171 | SA_REP     | 
|         172 | SA_REP     | 
|         173 | SA_REP     | 
|         174 | SA_REP     | 
|         175 | SA_REP     | 
|         176 | SA_REP     | 
|         177 | SA_REP     | 
|         178 | SA_REP     | 
|         179 | SA_REP     | 
|         180 | SH_CLERK   | 
|         181 | SH_CLERK   | 
|         182 | SH_CLERK   | 
|         183 | SH_CLERK   | 
|         184 | SH_CLERK   | 
|         185 | SH_CLERK   | 
|         186 | SH_CLERK   | 
|         187 | SH_CLERK   | 
|         188 | SH_CLERK   | 
|         189 | SH_CLERK   | 
|         190 | SH_CLERK   | 
|         191 | SH_CLERK   | 
|         192 | SH_CLERK   | 
|         193 | SH_CLERK   | 
|         194 | SH_CLERK   | 
|         195 | SH_CLERK   | 
|         196 | SH_CLERK   | 
|         197 | SH_CLERK   | 
|         198 | SH_CLERK   | 
|         199 | SH_CLERK   | 
|         200 | AD_ASST    | 
|         201 | MK_MAN     | 
|         202 | MK_REP     | 
|         203 | HR_REP     | 
|         204 | PR_REP     | 
|         205 | AC_MGR     | 
|         206 | AC_ACCOUNT | 
|         102 | IT_PROG    | 
|         101 | AC_ACCOUNT | 
|         101 | AC_MGR     | 
|         201 | MK_REP     | 
|         114 | ST_CLERK   | 
|         122 | ST_CLERK   | 
|         176 | SA_MAN     | 
|         200 | AC_ACCOUNT | 
|           0 |            | 
+-------------+------------+

Pictorial presentation of output

Here in the above picture shows, only the employee_id and job_id surrounded the red rectangle are same in employees and job_history table, so it comes once in the output but the other employee_id and job_id are different in both the tables, so they come each.

MySQL UNION ALL

The UNION ALL operator does not eliminate duplicate selected rows and returns all rows.

Pictorial presentation of UNION ALL operator

The UNION ALL operator returns all the rows from both the queries and no duplication elimination happens.

MySQL UNION vs UNION ALL

In MySQL the UNION operator returns the combined result from multiple SELECT statements into a single result set but exclude the duplicate rows where as the UNION ALL operator avoids the elimination of duplicate selected rows and returns all rows.

See the example below.

Example

If we want to display the present and previous details of jobs of all employees, and they may appear more than once, the following MySQL statement can be used.

Select employee_id, job_id,department_id
FROM employees
UNION ALL
Select employee_id,job_id,department_id
FROM job_history;

Sample Output:

+-------------+------------+---------------+
| employee_id | job_id     | department_id |
+-------------+------------+---------------+
|         100 | AD_PRES    |            90 | 
|         101 | AD_VP      |            90 | 
|         102 | AD_VP      |            90 | 
|         103 | IT_PROG    |            60 | 
|         104 | IT_PROG    |            60 | 
|         105 | IT_PROG    |            60 | 
|         106 | IT_PROG    |            60 | 
|         107 | IT_PROG    |            60 | 
|         108 | FI_MGR     |           100 | 
|         109 | FI_ACCOUNT |           100 | 
|         110 | FI_ACCOUNT |           100 | 
|         111 | FI_ACCOUNT |           100 | 
|         112 | FI_ACCOUNT |           100 | 
|         113 | FI_ACCOUNT |           100 | 
|         114 | PU_MAN     |            30 | 
|         115 | PU_CLERK   |            30 | 
|         116 | PU_CLERK   |            30 | 
|         117 | PU_CLERK   |            30 | 
|         118 | PU_CLERK   |            30 | 
|         119 | PU_CLERK   |            30 | 
|         120 | ST_MAN     |            50 | 
|         121 | ST_MAN     |            50 | 
|         122 | ST_MAN     |            50 | 
|         123 | ST_MAN     |            50 | 
|         124 | ST_MAN     |            50 | 
|         125 | ST_CLERK   |            50 | 
|         126 | ST_CLERK   |            50 | 
|         127 | ST_CLERK   |            50 | 
|         128 | ST_CLERK   |            50 | 
|         129 | ST_CLERK   |            50 | 
|         130 | ST_CLERK   |            50 | 
|         131 | ST_CLERK   |            50 | 
|         132 | ST_CLERK   |            50 | 
|         133 | ST_CLERK   |            50 | 
|         134 | ST_CLERK   |            50 | 
|         135 | ST_CLERK   |            50 | 
|         136 | ST_CLERK   |            50 | 
|         137 | ST_CLERK   |            50 | 
|         138 | ST_CLERK   |            50 | 
|         139 | ST_CLERK   |            50 | 
|         140 | ST_CLERK   |            50 | 
|         141 | ST_CLERK   |            50 | 
|         142 | ST_CLERK   |            50 | 
|         143 | ST_CLERK   |            50 | 
|         144 | ST_CLERK   |            50 | 
|         145 | SA_MAN     |            80 | 
|         146 | SA_MAN     |            80 | 
|         147 | SA_MAN     |            80 | 
|         148 | SA_MAN     |            80 | 
|         149 | SA_MAN     |            80 | 
|         150 | SA_REP     |            80 | 
|         151 | SA_REP     |            80 | 
|         152 | SA_REP     |            80 | 
|         153 | SA_REP     |            80 | 
|         154 | SA_REP     |            80 | 
|         155 | SA_REP     |            80 | 
|         156 | SA_REP     |            80 | 
|         157 | SA_REP     |            80 | 
|         158 | SA_REP     |            80 | 
|         159 | SA_REP     |            80 | 
|         160 | SA_REP     |            80 | 
|         161 | SA_REP     |            80 | 
|         162 | SA_REP     |            80 | 
|         163 | SA_REP     |            80 | 
|         164 | SA_REP     |            80 | 
|         165 | SA_REP     |            80 | 
|         166 | SA_REP     |            80 | 
|         167 | SA_REP     |            80 | 
|         168 | SA_REP     |            80 | 
|         169 | SA_REP     |            80 | 
|         170 | SA_REP     |            80 | 
|         171 | SA_REP     |            80 | 
|         172 | SA_REP     |            80 | 
|         173 | SA_REP     |            80 | 
|         174 | SA_REP     |            80 | 
|         175 | SA_REP     |            80 | 
|         176 | SA_REP     |            80 | 
|         177 | SA_REP     |            80 | 
|         178 | SA_REP     |             0 | 
|         179 | SA_REP     |            80 | 
|         180 | SH_CLERK   |            50 | 
|         181 | SH_CLERK   |            50 | 
|         182 | SH_CLERK   |            50 | 
|         183 | SH_CLERK   |            50 | 
|         184 | SH_CLERK   |            50 | 
|         185 | SH_CLERK   |            50 | 
|         186 | SH_CLERK   |            50 | 
|         187 | SH_CLERK   |            50 | 
|         188 | SH_CLERK   |            50 | 
|         189 | SH_CLERK   |            50 | 
|         190 | SH_CLERK   |            50 | 
|         191 | SH_CLERK   |            50 | 
|         192 | SH_CLERK   |            50 | 
|         193 | SH_CLERK   |            50 | 
|         194 | SH_CLERK   |            50 | 
|         195 | SH_CLERK   |            50 | 
|         196 | SH_CLERK   |            50 | 
|         197 | SH_CLERK   |            50 | 
|         198 | SH_CLERK   |            50 | 
|         199 | SH_CLERK   |            50 | 
|         200 | AD_ASST    |            10 | 
|         201 | MK_MAN     |            20 | 
|         202 | MK_REP     |            20 | 
|         203 | HR_REP     |            40 | 
|         204 | PR_REP     |            70 | 
|         205 | AC_MGR     |           110 | 
|         206 | AC_ACCOUNT |           110 | 
|         102 | IT_PROG    |            60 | 
|         101 | AC_ACCOUNT |           110 | 
|         101 | AC_MGR     |           110 | 
|         201 | MK_REP     |            20 | 
|         114 | ST_CLERK   |            50 | 
|         122 | ST_CLERK   |            50 | 
|         200 | AD_ASST    |            90 | 
|         176 | SA_REP     |            80 | 
|         176 | SA_MAN     |            80 | 
|         200 | AC_ACCOUNT |            90 | 
|           0 |            |             0 | 
+-------------+------------+---------------+
118 rows in set (0.11 sec)

Pictorial presentation of output

Here in the above picture shows, only the employee_id and job_id surrounded the red rectangle are same in employees and job_history table and they all have appeared in the result set. Here UNION ALL have to avoid the elimination of duplicate rows.

MySQL UNION DISTINCT

The DISTINCT clause with UNION produced nothing extra as the simple UNION done. From the UNION operator, we know that all rows will be displayed from both the queries except the duplicate are once.

Example

If we want to display the present and previous details of jobs of all employees once the following MySQL statement can be used.

SELECT DISTINCT employee_id, job_id
FROM employees
UNION
SELECT DISTINCT employee_id,job_id
FROM job_history;

Output :

The result will be same as the UNION operator do.

MySQL UNION ORDER BY

The ORDER BY clause with UNION arrange the rows in the result set in a specific order. The default order is ascending. The ORDER BY only used at the very end of the statement.

Example

If we want to display the present and previous details of jobs of all employees once the following MySQL statement can be used.


SELECT  employee_id, job_id
FROM employees
UNION
SELECT  employee_id,job_id
FROM job_history
ORDER BY employee_id;

The result will be same as the UNION operator do.

Sample Output:

+-------------+------------+
| employee_id | job_id     |
+-------------+------------+
|           0 |            | 
|         100 | AD_PRES    | 
|         101 | AC_ACCOUNT | 
|         101 | AC_MGR     | 
|         101 | AD_VP      | 
.....
|         107 | IT_PROG    | 
|         108 | FI_MGR     | 
|         113 | FI_ACCOUNT | 
|         114 | PU_MAN     | 
|         114 | ST_CLERK   | 
.....
|         128 | ST_CLERK   | 
|         129 | ST_CLERK   | 
|         134 | ST_CLERK   | 
|         135 | ST_CLERK   | 
.....
|         149 | SA_MAN     | 
|         150 | SA_REP     | 
|         158 | SA_REP     | 
|         159 | SA_REP     | 
.....
|         176 | SA_REP     | 
|         176 | SA_MAN     | 
|         177 | SA_REP     | 

|         200 | AC_ACCOUNT | 
|         200 | AD_ASST    | 
|         201 | MK_REP     | 
|         201 | MK_MAN     | 
.....
|         204 | PR_REP     | 
|         205 | AC_MGR     | 
|         206 | AC_ACCOUNT | 
+-------------+------------+
116 rows in set (0.00 sec)

Previous: MySQL basic select statement
Next: MySQL Aggregate Functions and Grouping Aggregate Functions and Grouping