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.
-- This SQL statement combines the results of two SELECT queries using the UNION operator.
-- The UNION operator removes duplicate rows from the combined result set.
SELECT employee_id, job_id
-- Specifies the columns to be selected in the query from the employees table: employee_id and job_id.
FROM employees
-- Specifies the table from which to select data, which is the employees table.
UNION
-- The UNION operator is used to combine the results of multiple SELECT queries.
Select employee_id, job_id
-- Specifies the columns to be selected in the query from the job_history table: employee_id and job_id.
FROM job_history;
-- Specifies the table from which to select data, which is the job_history table.
Explanation:
- The purpose of this SQL code is to retrieve the employee_id and job_id columns from both the employees and job_history tables and combine the results into a single result set, removing any duplicate rows.
- SELECT employee_id, job_id FROM employees: This line specifies the columns to be selected in the first SELECT query from the employees table: employee_id and job_id.
- UNION: The UNION operator is used to combine the results of two SELECT queries. It ensures that only distinct rows are included in the final result set.
- Select employee_id, job_id FROM job_history: This line specifies the columns to be selected in the second SELECT query from the job_history table: employee_id and job_id.
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.
-- This SQL statement combines the results of two SELECT queries using the UNION ALL operator.
-- The UNION ALL operator retains all rows from both SELECT queries, including duplicates.
SELECT employee_id, job_id, department_id
-- Specifies the columns to be selected in the query from the employees table: employee_id, job_id, and department_id.
FROM employees
-- Specifies the table from which to select data, which is the employees table.
UNION ALL
-- The UNION ALL operator is used to combine the results of multiple SELECT queries, retaining all rows, including duplicates.
SELECT employee_id, job_id, department_id
-- Specifies the columns to be selected in the query from the job_history table: employee_id, job_id, and department_id.
FROM job_history;
-- Specifies the table from which to select data, which is the job_history table.
Explanation:
- The purpose of this SQL code is to retrieve the employee_id, job_id, and department_id columns from both the employees and job_history tables and combine the results into a single result set, including all rows from both tables.
- SELECT employee_id, job_id, department_id FROM employees: This line specifies the columns to be selected in the first SELECT query from the employees table: employee_id, job_id, and department_id.
- UNION ALL: The UNION ALL operator is used to combine the results of two SELECT queries. Unlike the UNION operator, UNION ALL retains all rows from both queries, including duplicates.
- SELECT employee_id, job_id, department_id FROM job_history: This line specifies the columns to be selected in the second SELECT query from the job_history table: employee_id, job_id, and department_id.
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.
-- This SQL statement combines the results of two SELECT queries using the UNION operator.
-- The UNION operator removes duplicate rows from the combined result set.
SELECT DISTINCT employee_id, job_id
-- Specifies the columns to be selected in the query from the employees table: employee_id and job_id.
-- The DISTINCT keyword ensures that only unique combinations of employee_id and job_id are included in the result set.
FROM employees
-- Specifies the table from which to select data, which is the employees table.
UNION
-- The UNION operator is used to combine the results of multiple SELECT queries.
SELECT DISTINCT employee_id, job_id
-- Specifies the columns to be selected in the query from the job_history table: employee_id and job_id.
-- The DISTINCT keyword ensures that only unique combinations of employee_id and job_id are included in the result set.
FROM job_history;
-- Specifies the table from which to select data, which is the job_history table.
Explanation:
- The purpose of this SQL code is to retrieve unique combinations of employee_id and job_id from both the employees and job_history tables and combine the results into a single result set, removing any duplicate rows.
- SELECT DISTINCT employee_id, job_id FROM employees: This line specifies the columns to be selected in the first SELECT query from the employees table: employee_id and job_id.
- The DISTINCT keyword ensures that only unique combinations of employee_id and job_id are included in the result set.
- UNION: The UNION operator is used to combine the results of two SELECT queries. It ensures that only distinct rows are included in the final result set.
- SELECT DISTINCT employee_id, job_id FROM job_history: This line specifies the columns to be selected in the second SELECT query from the job_history table: employee_id and job_id. The DISTINCT keyword ensures that only unique combinations of employee_id and job_id are included in the result set.
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.
-- This SQL statement combines the results of two SELECT queries using the UNION operator.
-- The UNION operator removes duplicate rows from the combined result set.
-- The final result set is then ordered by the employee_id column.
SELECT employee_id, job_id
-- Specifies the columns to be selected in the query from the employees table: employee_id and job_id.
FROM employees
-- Specifies the table from which to select data, which is the employees table.
UNION
-- The UNION operator is used to combine the results of multiple SELECT queries.
SELECT employee_id, job_id
-- Specifies the columns to be selected in the query from the job_history table: employee_id and job_id.
FROM job_history
-- Specifies the table from which to select data, which is the job_history table.
ORDER BY employee_id;
-- Specifies the ordering of the result set based on the employee_id column.
Explanation:
- The purpose of this SQL code is to retrieve the employee_id and job_id columns from both the employees and job_history tables, combine the results into a single result set using the UNION operator (which removes duplicates), and finally order the result set based on the employee_id column.
- SELECT employee_id, job_id FROM employees: This line specifies the columns to be selected in the first SELECT query from the employees table: employee_id and job_id.
- UNION: The UNION operator is used to combine the results of two SELECT queries. It ensures that only distinct rows are included in the final result set.
- SELECT employee_id, job_id FROM job_history: This line specifies the columns to be selected in the second SELECT query from the job_history table: employee_id and job_id.
- ORDER BY employee_id: This line specifies the ordering of the result set based on the employee_id column, arranging the rows in ascending order based on 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