MySQL UNION
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook