SQLite UNION
Description
In SQLite, 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 sqlite statement can be used.
Select employee_id, job_id
FROM employees
UNION
Select employee_id,job_id
FROM job_history;
Relational Algebra Expression:
Relational Algebra Tree:
Sample Output:
employee_id job_id ----------- ---------- 100 AD_PRES 101 AC_ACCOUNT 101 AC_MGR 101 AD_VP 102 AD_VP 102 IT_PROG 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 114 ST_CLERK 115 PU_CLERK 116 PU_CLERK 117 PU_CLERK 118 PU_CLERK 119 PU_CLERK 120 ST_MAN 121 ST_MAN 122 ST_CLERK 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_MAN 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 AC_ACCOUNT 200 AD_ASST 201 MK_MAN 201 MK_REP 202 MK_REP 203 HR_REP 204 PR_REP 205 AC_MGR 206 AC_ACCOUNT
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.
SQLite 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.
SQLite UNION vs UNION ALL
In SQLite 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 sqlite 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;
Relational Algebra Expression:
Relational Algebra Tree:
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 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
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.
SQLite 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 sqlite 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.
SQLite 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 sqlite statement can be used.
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id,job_id
FROM job_history
ORDER BY employee_id;
Output:
The result will be same as the UNION operator do.
employee_id job_id ----------- ---------- 100 AD_PRES 101 AC_ACCOUNT 101 AC_MGR 101 AD_VP 102 AD_VP 102 IT_PROG 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 114 ST_CLERK 115 PU_CLERK 116 PU_CLERK 117 PU_CLERK 118 PU_CLERK 119 PU_CLERK 120 ST_MAN 121 ST_MAN 122 ST_CLERK 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_MAN 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 AC_ACCOUNT 200 AD_ASST 201 MK_MAN 201 MK_REP 202 MK_REP 203 HR_REP 204 PR_REP 205 AC_MGR 206 AC_ACCOUNT
Previous:
Delete
Next:
Collating Sequences
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework