Union and Union All in Oracle SQL | Oracle SQL Tutorials -24

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

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *