{"id":16813,"date":"2020-07-08T08:49:43","date_gmt":"2020-07-08T08:49:43","guid":{"rendered":"https:\/\/ittutorial.org\/?p=16813"},"modified":"2020-07-08T08:49:43","modified_gmt":"2020-07-08T08:49:43","slug":"union-and-union-all-in-oracle-sql-oracle-sql-tutorials-24","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/union-and-union-all-in-oracle-sql-oracle-sql-tutorials-24\/","title":{"rendered":"Union and Union All in Oracle SQL | Oracle SQL Tutorials -24"},"content":{"rendered":"<p align=\"left\">Hi,<\/p>\n<p align=\"left\">I will explain Union and Union All in Oracle SQL in this post of Oracle SQL Tutorial series.<\/p>\n<p align=\"left\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-16079\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial.png\" alt=\"\" width=\"387\" height=\"311\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial.png 387w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial-300x241.png 300w\" sizes=\"auto, (max-width: 387px) 100vw, 387px\" \/><\/p>\n<p align=\"left\"><!--more--><\/p>\n<p align=\"left\">\n<p align=\"left\"><span style=\"color: #ff0000;\"><strong>Read the previous post of this tutorial series before this.<\/strong><\/span><\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"CBzz7bD7NC\"><p><a href=\"https:\/\/ittutorial.org\/sql-full-outer-join-in-oracle-sql-oracle-sql-tutorials-23\/\">SQL Full Outer Join in Oracle SQL | Oracle SQL Tutorials -23<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;SQL Full Outer Join in Oracle SQL | Oracle SQL Tutorials -23&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/sql-full-outer-join-in-oracle-sql-oracle-sql-tutorials-23\/embed\/#?secret=OSvGiALZKd#?secret=CBzz7bD7NC\" data-secret=\"CBzz7bD7NC\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Union Operator<\/span><\/h4>\n<p>Union operator is very popular operator in Oracle SQL that is used to combine two or more result set of Select statements.<\/p>\n<p>Union operator is valid for similar data types columns and they should be in the same order.<\/p>\n<p>&nbsp;<\/p>\n<h5>\n<span style=\"color: #ff0000;\">UNION Operator Syntax<\/span><\/h5>\n<p>Union operator syntax is as follows.<\/p>\n<pre>\r\nSELECT column1,column2,column(n)... FROM table1\r\n<span style=\"color: #ff0000;\"><strong>UNION<\/strong><\/span>\r\nSELECT column1,column2,column(n)... FROM table2;<\/pre>\n<p>&nbsp;<\/p>\n<h5>\n<span style=\"color: #ff0000;\">UNION ALL Operator<\/span><\/h5>\n<p>Union All operator is like Union operator that is combine two or more result set of Select statements.<\/p>\n<p>The only difference between Union and Union All operator is duplicated values in the result set.<\/p>\n<p>&nbsp;<\/p>\n<h5><span style=\"color: #ff0000;\">UNION ALL Syntax<\/span><\/h5>\n<p>Union All operator syntax is as follows.<\/p>\n<pre>\r\nSELECT column1,column2,column(n)... FROM table1\r\n<strong><span style=\"color: #ff0000;\">UNION All<\/span><\/strong>\r\nSELECT column1,column2,column(n)... FROM table2;<\/pre>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11597\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/union-all-vs-union.png\" alt=\"\" width=\"992\" height=\"481\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/union-all-vs-union.png 992w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/union-all-vs-union-300x145.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/union-all-vs-union-768x372.png 768w\" sizes=\"auto, (max-width: 992px) 100vw, 992px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>You should use Union all instead of Union.<\/p>\n<p>If using union all is possible, you should prefer it instead of union, because Union are doing expensive sort to remove duplicate rows.<\/p>\n<p>&nbsp;<\/p>\n<p>Let&#8217;s make an example about Union and Union All.<\/p>\n<p>There are common column between hr.employees and hr.job_history table like employee_id, job_id columns.<\/p>\n<p>Union example is as follows.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; SELECT employee_id, job_id\r\n  2     FROM   hr.employees\r\n  3     <span style=\"color: #ff0000;\"><strong>UNION<\/strong><\/span>\r\n  4     SELECT employee_id, job_id\r\n  5     FROM   hr.job_history;\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        101 AC_ACCOUNT\r\n        101 AC_MGR\r\n        101 AD_VP\r\n        102 AD_VP\r\n        102 IT_PROG\r\n        103 IT_PROG\r\n        104 IT_PROG\r\n        105 IT_PROG\r\n        106 IT_PROG\r\n        107 IT_PROG\r\n        108 FI_MGR\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        109 FI_ACCOUNT\r\n        110 FI_ACCOUNT\r\n        111 FI_ACCOUNT\r\n        112 FI_ACCOUNT\r\n        113 FI_ACCOUNT\r\n        114 PU_MAN\r\n        114 ST_CLERK\r\n        115 PU_CLERK\r\n        116 PU_CLERK\r\n        117 PU_CLERK\r\n        118 PU_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        119 PU_CLERK\r\n        120 ST_MAN\r\n        121 ST_MAN\r\n        122 ST_CLERK\r\n        122 ST_MAN\r\n        123 ST_MAN\r\n        124 ST_MAN\r\n        125 ST_CLERK\r\n        126 ST_CLERK\r\n        127 ST_CLERK\r\n        128 ST_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        129 ST_CLERK\r\n        130 ST_CLERK\r\n        131 ST_CLERK\r\n        132 ST_CLERK\r\n        133 ST_CLERK\r\n        134 ST_CLERK\r\n        135 ST_CLERK\r\n        136 ST_CLERK\r\n        137 ST_CLERK\r\n        138 ST_CLERK\r\n        139 ST_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        140 ST_CLERK\r\n        141 ST_CLERK\r\n        142 ST_CLERK\r\n        143 ST_CLERK\r\n        144 ST_CLERK\r\n        145 SA_MAN\r\n        146 SA_MAN\r\n        147 SA_MAN\r\n        148 SA_MAN\r\n        149 SA_MAN\r\n        150 SA_REP\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        151 SA_REP\r\n        152 SA_REP\r\n        153 SA_REP\r\n        154 SA_REP\r\n        155 SA_REP\r\n        156 SA_REP\r\n        157 SA_REP\r\n        158 SA_REP\r\n        159 SA_REP\r\n        160 SA_REP\r\n        161 SA_REP\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        162 SA_REP\r\n        163 SA_REP\r\n        164 SA_REP\r\n        165 SA_REP\r\n        166 SA_REP\r\n        167 SA_REP\r\n        168 SA_REP\r\n        169 SA_REP\r\n        170 SA_REP\r\n        171 SA_REP\r\n        172 SA_REP\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        173 SA_REP\r\n        174 SA_REP\r\n        175 SA_REP\r\n        176 SA_MAN\r\n        176 SA_REP\r\n        177 SA_REP\r\n        178 SA_REP\r\n        179 SA_REP\r\n        180 SH_CLERK\r\n        181 SH_CLERK\r\n        182 SH_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        183 SH_CLERK\r\n        184 SH_CLERK\r\n        185 SH_CLERK\r\n        186 SH_CLERK\r\n        187 SH_CLERK\r\n        188 SH_CLERK\r\n        189 SH_CLERK\r\n        190 SH_CLERK\r\n        191 SH_CLERK\r\n        192 SH_CLERK\r\n        193 SH_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        194 SH_CLERK\r\n        195 SH_CLERK\r\n        196 SH_CLERK\r\n        197 SH_CLERK\r\n        198 SH_CLERK\r\n        199 SH_CLERK\r\n        200 AC_ACCOUNT\r\n        200 AD_ASST\r\n        201 MK_MAN\r\n        201 MK_REP\r\n        202 MK_REP\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        203 HR_REP\r\n        204 PR_REP\r\n        205 AC_MGR\r\n        206 AC_ACCOUNT\r\n\r\n114 rows selected.\r\n\r\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>Union result of the hr.employees and hr.job_history table is 114 rows. There is no duplicated rows in this result set.<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>Union All example is as follows.<\/p>\n<pre><\/pre>\n<pre>SQL&gt; \r\nSQL&gt; SELECT employee_id, job_id\r\n       FROM   hr.employees\r\n       UNION ALL\r\n       SELECT employee_id, job_id\r\n       FROM   hr.job_history;\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        206 AC_ACCOUNT\r\n        205 AC_MGR\r\n        200 AD_ASST\r\n        101 AD_VP\r\n        102 AD_VP\r\n        109 FI_ACCOUNT\r\n        110 FI_ACCOUNT\r\n        111 FI_ACCOUNT\r\n        112 FI_ACCOUNT\r\n        113 FI_ACCOUNT\r\n        108 FI_MGR\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        203 HR_REP\r\n        103 IT_PROG\r\n        104 IT_PROG\r\n        105 IT_PROG\r\n        106 IT_PROG\r\n        107 IT_PROG\r\n        201 MK_MAN\r\n        202 MK_REP\r\n        204 PR_REP\r\n        115 PU_CLERK\r\n        116 PU_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        117 PU_CLERK\r\n        118 PU_CLERK\r\n        119 PU_CLERK\r\n        114 PU_MAN\r\n        145 SA_MAN\r\n        146 SA_MAN\r\n        147 SA_MAN\r\n        148 SA_MAN\r\n        149 SA_MAN\r\n        150 SA_REP\r\n        151 SA_REP\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        152 SA_REP\r\n        153 SA_REP\r\n        154 SA_REP\r\n        155 SA_REP\r\n        156 SA_REP\r\n        157 SA_REP\r\n        158 SA_REP\r\n        159 SA_REP\r\n        160 SA_REP\r\n        161 SA_REP\r\n        162 SA_REP\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        163 SA_REP\r\n        164 SA_REP\r\n        165 SA_REP\r\n        166 SA_REP\r\n        167 SA_REP\r\n        168 SA_REP\r\n        169 SA_REP\r\n        170 SA_REP\r\n        171 SA_REP\r\n        172 SA_REP\r\n        173 SA_REP\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        174 SA_REP\r\n        175 SA_REP\r\n        176 SA_REP\r\n        177 SA_REP\r\n        178 SA_REP\r\n        179 SA_REP\r\n        180 SH_CLERK\r\n        181 SH_CLERK\r\n        182 SH_CLERK\r\n        183 SH_CLERK\r\n        184 SH_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        185 SH_CLERK\r\n        186 SH_CLERK\r\n        187 SH_CLERK\r\n        188 SH_CLERK\r\n        189 SH_CLERK\r\n        190 SH_CLERK\r\n        191 SH_CLERK\r\n        192 SH_CLERK\r\n        193 SH_CLERK\r\n        194 SH_CLERK\r\n        195 SH_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        196 SH_CLERK\r\n        197 SH_CLERK\r\n        198 SH_CLERK\r\n        199 SH_CLERK\r\n        125 ST_CLERK\r\n        126 ST_CLERK\r\n        127 ST_CLERK\r\n        128 ST_CLERK\r\n        129 ST_CLERK\r\n        130 ST_CLERK\r\n        131 ST_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        132 ST_CLERK\r\n        133 ST_CLERK\r\n        134 ST_CLERK\r\n        135 ST_CLERK\r\n        136 ST_CLERK\r\n        137 ST_CLERK\r\n        138 ST_CLERK\r\n        139 ST_CLERK\r\n        140 ST_CLERK\r\n        141 ST_CLERK\r\n        142 ST_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        143 ST_CLERK\r\n        144 ST_CLERK\r\n        120 ST_MAN\r\n        121 ST_MAN\r\n        122 ST_MAN\r\n        123 ST_MAN\r\n        124 ST_MAN\r\n        101 AC_ACCOUNT\r\n        200 AC_ACCOUNT\r\n        101 AC_MGR\r\n        200 AD_ASST\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        102 IT_PROG\r\n        201 MK_REP\r\n        176 SA_MAN\r\n        176 SA_REP\r\n        114 ST_CLERK\r\n        122 ST_CLERK\r\n\r\n116 rows selected.\r\n\r\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>Union All result of the hr.employees and hr.job_history table is 116 rows. There are 2 duplicated rows in this result set.<\/p>\n<p>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.<\/p>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Dou want to learn Oracle SQL Tutorial for Beginners, then read the following articles.<\/span><\/h4>\n<blockquote class=\"wp-embedded-content\" data-secret=\"IhX2Immacu\"><p><a href=\"https:\/\/ittutorial.org\/oracle-sql-tutorials-for-beginners-learn-sql-tutorials-sql-course\/\">Oracle SQL Tutorials For Beginners &#8211; Learn Oracle SQL from scratch with Oracle SQL Online Course<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Oracle SQL Tutorials For Beginners &#8211; Learn Oracle SQL from scratch with Oracle SQL Online Course&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/oracle-sql-tutorials-for-beginners-learn-sql-tutorials-sql-course\/embed\/#?secret=JpLKBbNwlp#?secret=IhX2Immacu\" data-secret=\"IhX2Immacu\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, I will explain Union and Union All in Oracle SQL in this post of Oracle SQL Tutorial series.<\/p>\n","protected":false},"author":1,"featured_media":11597,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3019],"tags":[12017,12012,9037,12018,11351,11304,3001,11302,12024,12025,2673,11301,12011,12010,12021,12020,12009,3715,12013,12014,12015,12007,12022,12016,12008,12019,12023],"class_list":["post-16813","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle-sql","tag-difference-between-union-and-union-all-interview-questions","tag-how-to-union-two-tables-with-different-columns-in-oracle","tag-oracle-dba-tutorial","tag-oracle-join","tag-oracle-live-sql","tag-oracle-sql-developer-tutorial","tag-oracle-sql-tutorial","tag-oracle-sql-tutorial-pdf","tag-oracle-sql-union","tag-oracle-sql-union-all","tag-oracle-tutorial","tag-oracle-tutorial-pdf","tag-oracle-union-all-example","tag-oracle-union-example","tag-oracle-union-order-by","tag-oracle-union-vs-union-all","tag-oracle-union-vs-union-all-performance","tag-pl-sql-tutorial","tag-sql-union","tag-sql-union-all","tag-union-all-in-oracle-with-group-by","tag-union-all-in-sql","tag-union-all-oracle","tag-union-in-oracle-sql-with-where-clause","tag-union-in-sql","tag-union-sql","tag-union-with-group-by-oracle"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/union-all-vs-union.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16813","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=16813"}],"version-history":[{"count":1,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16813\/revisions"}],"predecessor-version":[{"id":16814,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16813\/revisions\/16814"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/11597"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=16813"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=16813"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=16813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}