Intersect and Minus in Oracle SQL | Oracle SQL Tutorials -25

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.

 

 

 

 

Intersect Operator

Intersect operator is used to find the intersection set of data in two data sets .

If you need intersection of two tables ( Common rows ), you should use Intersect operator.

 

Intersect Operator Syntax

Intersect operator syntax is as follows.

SELECT column1,column2,column(n)... FROM table1
INTERSECT
SELECT column1,column2,column(n)... FROM table2;

 

Intersect operator example is as follows.

SQL> Select first_name,last_name,salary from hr.employees where salary between 2000 and 5000
       intersect
       select first_name,last_name,salary from hr.employees where salary between 4000 and 7500;

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Alana                Walsh                           4100
Alexander            Khoo                            4100
Anthony              Cabrio                          4000
Britney              Everett                         4900
Curtis               Davies                          4100
Jason                Mallin                          4300
Jean                 Fleaur                          4100
Jennifer             Dilly                           4600
Julia                Dellinger                       4400
Julia                Nayer                           4200
Kelly                Chung                           4800

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Kevin                Feeney                          4000
Laura                Bissot                          4300
Renske               Ladwig                          4600
Samuel               McCain                          4200
Sarah                Bell                            5000
Stephen              Stiles                          4200
Trenna               Rajs                            4500
Winston              Taylor                          4200

19 rows selected.

SQL>

 

 

 

Minus Operator

Minus operator is used to indicate the difference between the data in the two data sets .

If you need difference of two tables ( different rows ), you should use minus operator.

 

Minus of A table from B table is in light orange color as follows.

 

 

 

Minus Syntax

Minus operator syntax is as follows.

SELECT column1,column2,column(n)... FROM table1
MINUS
SELECT column1,column2,column(n)... FROM table2;

 

 

 

 

SQL> SELECT employee_id, job_id
       FROM   hr.employees
       minus
       SELECT employee_id, job_id
       FROM   hr.job_history;

EMPLOYEE_ID JOB_ID
----------- ----------
        101 AD_VP
        102 AD_VP
        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

EMPLOYEE_ID JOB_ID
----------- ----------
        112 FI_ACCOUNT
        113 FI_ACCOUNT
        114 PU_MAN
        115 PU_CLERK
        116 PU_CLERK
        117 PU_CLERK
        118 PU_CLERK
        119 PU_CLERK
        120 ST_MAN
        121 ST_MAN
        122 ST_MAN

EMPLOYEE_ID JOB_ID
----------- ----------
        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

EMPLOYEE_ID JOB_ID
----------- ----------
        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

EMPLOYEE_ID JOB_ID
----------- ----------
        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

EMPLOYEE_ID JOB_ID
----------- ----------
        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

EMPLOYEE_ID JOB_ID
----------- ----------
        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
        177 SA_REP
        178 SA_REP

EMPLOYEE_ID JOB_ID
----------- ----------
        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

EMPLOYEE_ID JOB_ID
----------- ----------
        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
        201 MK_MAN

EMPLOYEE_ID JOB_ID
----------- ----------
        202 MK_REP
        203 HR_REP
        204 PR_REP
        205 AC_MGR
        206 AC_ACCOUNT

104 rows selected.

SQL>

 

 

You should use MINUS instead of EXISTS

Minus operator is mostly running in a faster execution plan than NOT IN and NOT Exists operator.

 

 

Dou want to learn Oracle SQL Tutorial for Beginners, then read the following articles.

 110 views last month,  1 views today

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 mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply