{"id":16815,"date":"2020-07-08T09:40:34","date_gmt":"2020-07-08T09:40:34","guid":{"rendered":"https:\/\/ittutorial.org\/?p=16815"},"modified":"2020-07-08T09:40:34","modified_gmt":"2020-07-08T09:40:34","slug":"intersect-and-minus-in-oracle-sql-oracle-sql-tutorials-25","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/intersect-and-minus-in-oracle-sql-oracle-sql-tutorials-25\/","title":{"rendered":"Intersect and Minus in Oracle SQL | Oracle SQL Tutorials -25"},"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=\"GP5sRDOxVU\"><p><a href=\"https:\/\/ittutorial.org\/union-and-union-all-in-oracle-sql-oracle-sql-tutorials-24\/\">Union and Union All in Oracle SQL | Oracle SQL Tutorials -24<\/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;Union and Union All in Oracle SQL | Oracle SQL Tutorials -24&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/union-and-union-all-in-oracle-sql-oracle-sql-tutorials-24\/embed\/#?secret=ABzpwwi4J9#?secret=GP5sRDOxVU\" data-secret=\"GP5sRDOxVU\" 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;\">Intersect Operator<\/span><\/h4>\n<p align=\"left\"><span lang=\"en-US\">Intersect operator is used to find the intersection set of data in two data sets .<\/span><\/p>\n<p>If you need intersection of two tables ( Common rows ), you should use Intersect operator.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-16816\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/intersect-sql-oracle.png\" alt=\"\" width=\"517\" height=\"331\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/intersect-sql-oracle.png 517w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/intersect-sql-oracle-300x192.png 300w\" sizes=\"auto, (max-width: 517px) 100vw, 517px\" \/><\/p>\n<p>&nbsp;<\/p>\n<h5><span style=\"color: #ff0000;\">Intersect Operator Syntax<\/span><\/h5>\n<p>Intersect operator syntax is as follows.<\/p>\n<pre>SELECT column1,column2,column(n)... FROM table1\r\n<span style=\"color: #ff0000;\"><strong>INTERSECT<\/strong><\/span>\r\nSELECT column1,column2,column(n)... FROM table2;<\/pre>\n<p>&nbsp;<\/p>\n<p>Intersect operator example is as follows.<\/p>\n<pre>SQL&gt; Select first_name,last_name,salary from hr.employees where salary between 2000 and 5000\r\n       <span style=\"color: #ff0000;\"><strong>intersect<\/strong><\/span>\r\n       select first_name,last_name,salary from hr.employees where salary between 4000 and 7500;\r\n\r\nFIRST_NAME           LAST_NAME                     SALARY\r\n-------------------- ------------------------- ----------\r\nAlana                Walsh                           4100\r\nAlexander            Khoo                            4100\r\nAnthony              Cabrio                          4000\r\nBritney              Everett                         4900\r\nCurtis               Davies                          4100\r\nJason                Mallin                          4300\r\nJean                 Fleaur                          4100\r\nJennifer             Dilly                           4600\r\nJulia                Dellinger                       4400\r\nJulia                Nayer                           4200\r\nKelly                Chung                           4800\r\n\r\nFIRST_NAME           LAST_NAME                     SALARY\r\n-------------------- ------------------------- ----------\r\nKevin                Feeney                          4000\r\nLaura                Bissot                          4300\r\nRenske               Ladwig                          4600\r\nSamuel               McCain                          4200\r\nSarah                Bell                            5000\r\nStephen              Stiles                          4200\r\nTrenna               Rajs                            4500\r\nWinston              Taylor                          4200\r\n\r\n19 rows selected.\r\n\r\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h5><span style=\"color: #ff0000;\">Minus Operator<\/span><\/h5>\n<p>Minus operator is u<span lang=\"en-US\">sed to indicate the difference between the data in the two data sets . <\/span><\/p>\n<p>If you need difference of two tables ( different rows ), you should use minus operator.<\/p>\n<p>&nbsp;<\/p>\n<p>Minus of A table from B table is in light orange color as follows.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-16817\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/minus-sql-oracle.png\" alt=\"\" width=\"500\" height=\"321\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/minus-sql-oracle.png 500w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/minus-sql-oracle-300x193.png 300w\" sizes=\"auto, (max-width: 500px) 100vw, 500px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<h5><span style=\"color: #ff0000;\">Minus Syntax<\/span><\/h5>\n<p>Minus operator syntax is as follows.<\/p>\n<pre>SELECT column1,column2,column(n)... FROM table1\r\n<strong><span style=\"color: #ff0000;\">MINUS<\/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-16818\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/minus-and-intersect-oracle-sql.jpg\" alt=\"\" width=\"476\" height=\"316\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/minus-and-intersect-oracle-sql.jpg 476w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/minus-and-intersect-oracle-sql-300x199.jpg 300w\" sizes=\"auto, (max-width: 476px) 100vw, 476px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; SELECT employee_id, job_id\r\n       FROM   hr.employees\r\n       minus\r\n       SELECT employee_id, job_id\r\n       FROM   hr.job_history;\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        101 AD_VP\r\n        102 AD_VP\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        109 FI_ACCOUNT\r\n        110 FI_ACCOUNT\r\n        111 FI_ACCOUNT\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        112 FI_ACCOUNT\r\n        113 FI_ACCOUNT\r\n        114 PU_MAN\r\n        115 PU_CLERK\r\n        116 PU_CLERK\r\n        117 PU_CLERK\r\n        118 PU_CLERK\r\n        119 PU_CLERK\r\n        120 ST_MAN\r\n        121 ST_MAN\r\n        122 ST_MAN\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\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        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\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\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        143 ST_CLERK\r\n        144 ST_CLERK\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\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        152 SA_REP\r\n        153 SA_REP\r\n        154 SA_REP\r\n        155 SA_REP\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\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        163 SA_REP\r\n        164 SA_REP\r\n        165 SA_REP\r\n        166 SA_REP\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\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        174 SA_REP\r\n        175 SA_REP\r\n        177 SA_REP\r\n        178 SA_REP\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\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        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\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\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        196 SH_CLERK\r\n        197 SH_CLERK\r\n        198 SH_CLERK\r\n        199 SH_CLERK\r\n        201 MK_MAN\r\n\r\nEMPLOYEE_ID JOB_ID\r\n----------- ----------\r\n        202 MK_REP\r\n        203 HR_REP\r\n        204 PR_REP\r\n        205 AC_MGR\r\n        206 AC_ACCOUNT\r\n\r\n104 rows selected.\r\n\r\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>You should use MINUS instead of EXISTS<\/p>\n<p>Minus operator is mostly running in a faster execution plan than NOT IN and NOT Exists operator.<\/p>\n<p>&nbsp;<\/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=\"YAUxn21vc7\"><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=IWZFY31kNy#?secret=YAUxn21vc7\" data-secret=\"YAUxn21vc7\" 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":16079,"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":[12032,12030,12027,12041,9037,12037,12038,12036,11351,12040,12026,12042,12031,11304,12029,12034,3001,11302,2673,11301,12035,3715,12028,12039,12033],"class_list":["post-16815","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle-sql","tag-alternative-to-minus-operator-in-oracle","tag-minus-in-oracle","tag-minus-in-sql","tag-minus-operator-in-sql-how-to-subtract-two-values-in-sql","tag-oracle-dba-tutorial","tag-oracle-except","tag-oracle-intersect-ignore-null","tag-oracle-intersect-opposite","tag-oracle-live-sql","tag-oracle-minus","tag-oracle-minus-example","tag-oracle-minus-not-working","tag-oracle-minus-vs-not-in","tag-oracle-sql-developer-tutorial","tag-oracle-sql-intersect","tag-oracle-sql-minus","tag-oracle-sql-tutorial","tag-oracle-sql-tutorial-pdf","tag-oracle-tutorial","tag-oracle-tutorial-pdf","tag-oracle-union","tag-pl-sql-tutorial","tag-sql-intersect","tag-sql-minus","tag-sql-minus-vs-except"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16815","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=16815"}],"version-history":[{"count":1,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16815\/revisions"}],"predecessor-version":[{"id":16819,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16815\/revisions\/16819"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/16079"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=16815"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=16815"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=16815"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}