Hi,
I will explain Union and Union All in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
SQL Full Outer Join in Oracle SQL | Oracle SQL Tutorials -23
Union Operator
Union operator is very popular operator in Oracle SQL that is used to combine two or more result set of Select statements.
Union operator is valid for similar data types columns and they should be in the same order.
UNION Operator Syntax
Union operator syntax is as follows.
SELECT column1,column2,column(n)... FROM table1
UNION
SELECT column1,column2,column(n)... FROM table2;
UNION ALL Operator
Union All operator is like Union operator that is combine two or more result set of Select statements.
The only difference between Union and Union All operator is duplicated values in the result set.
UNION ALL Syntax
Union All operator syntax is as follows.
SELECT column1,column2,column(n)... FROM table1
UNION All
SELECT column1,column2,column(n)... FROM table2;
You should use Union all instead of Union.
If using union all is possible, you should prefer it instead of union, because Union are doing expensive sort to remove duplicate rows.
Let’s make an example about Union and Union All.
There are common column between hr.employees and hr.job_history table like employee_id, job_id columns.
Union example is as follows.
SQL> SELECT employee_id, job_id
2 FROM hr.employees
3 UNION
4 SELECT employee_id, job_id
5 FROM hr.job_history;
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
203 HR_REP
204 PR_REP
205 AC_MGR
206 AC_ACCOUNT
114 rows selected.
SQL>
Union result of the hr.employees and hr.job_history table is 114 rows. There is no duplicated rows in this result set.
Union All example is as follows.
SQL>
SQL> SELECT employee_id, job_id
FROM hr.employees
UNION ALL
SELECT employee_id, job_id
FROM hr.job_history;
EMPLOYEE_ID JOB_ID
----------- ----------
206 AC_ACCOUNT
205 AC_MGR
200 AD_ASST
101 AD_VP
102 AD_VP
109 FI_ACCOUNT
110 FI_ACCOUNT
111 FI_ACCOUNT
112 FI_ACCOUNT
113 FI_ACCOUNT
108 FI_MGR
EMPLOYEE_ID JOB_ID
----------- ----------
203 HR_REP
103 IT_PROG
104 IT_PROG
105 IT_PROG
106 IT_PROG
107 IT_PROG
201 MK_MAN
202 MK_REP
204 PR_REP
115 PU_CLERK
116 PU_CLERK
EMPLOYEE_ID JOB_ID
----------- ----------
117 PU_CLERK
118 PU_CLERK
119 PU_CLERK
114 PU_MAN
145 SA_MAN
146 SA_MAN
147 SA_MAN
148 SA_MAN
149 SA_MAN
150 SA_REP
151 SA_REP
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
196 SH_CLERK
197 SH_CLERK
198 SH_CLERK
199 SH_CLERK
125 ST_CLERK
126 ST_CLERK
127 ST_CLERK
128 ST_CLERK
129 ST_CLERK
130 ST_CLERK
131 ST_CLERK
EMPLOYEE_ID JOB_ID
----------- ----------
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
EMPLOYEE_ID JOB_ID
----------- ----------
143 ST_CLERK
144 ST_CLERK
120 ST_MAN
121 ST_MAN
122 ST_MAN
123 ST_MAN
124 ST_MAN
101 AC_ACCOUNT
200 AC_ACCOUNT
101 AC_MGR
200 AD_ASST
EMPLOYEE_ID JOB_ID
----------- ----------
102 IT_PROG
201 MK_REP
176 SA_MAN
176 SA_REP
114 ST_CLERK
122 ST_CLERK
116 rows selected.
SQL>
Union All result of the hr.employees and hr.job_history table is 116 rows. There are 2 duplicated rows in this result set.
Be careful that if using union all is possible, you should prefer it instead of union, because Union are doing expensive sort to remove duplicate rows.
Dou want to learn Oracle SQL Tutorial for Beginners, then read the following articles.
Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course