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.
Union and Union All in Oracle SQL | Oracle SQL Tutorials -24
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.
Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course