Hi,
I will explain SQL Subquery and Nested Queries in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
Intersect and Minus in Oracle SQL | Oracle SQL Tutorials -25
Sub-Query
Subquery is used frequently in SQL Statements that is a SQL Query within a query. Subqueries are nested queries.
Subqueries are frequently used by developers and it is very important to create complex SQL Statements for Reporting.
Subqueries return a list of records and main query use them. Subqueries must be enclosed with the parenthesis
Let’s make an example about Subquery usage as follows.
Firstly find the salary of Abel as follows.
SQL> SELECT salary
2 FROM hr.employees
3 WHERE last_name = ‘Abel’;
SALARY
———-
12000
SQL>
Now we can use this query as subquery as follows.
List the employees whose salary are greater than Abel as follows.
SQL> SELECT first_name,last_name, salary 2 FROM hr.employees 3 WHERE salary > 4 (SELECT salary 5 FROM hr.employees 6 WHERE last_name = 'Abel'); FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Neena Kochhar 23000 Lex De Haan 23000 Nancy Greenberg 13008 John Russell 15000 Karen Partners 14500 Alberto Errazuriz 13000 Lisa Ozer 12500 Michael Hartstein 14000 Shelley Higgins 13008 9 rows selected. SQL>
Abel’s salary was 12.000, and all employees whose salary is greater than 12.000 are listed.
You can use multiple sub query in a single SQL Statement as follows.
List the employees whose job id equals to the Austin and salary is greater than Austin as follows.
SQL> SELECT first_name,last_name, job_id, salary 2 FROM hr.employees WHERE job_id = 3 (SELECT job_id FROM hr.employees 4 WHERE last_name = 'Austin') 5 AND salary > 6 (SELECT salary FROM hr.employees 7 WHERE last_name = 'Austin'); FIRST_NAME LAST_NAME JOB_ID SALARY -------------------- ------------------------- ---------- ---------- Alexander Hunold IT_PROG 10000 Bruce Ernst IT_PROG 7000 SQL>
You can use the subqueries with the Aggregate functions as follows.
List the details of employee who has the minimum salary.
SQL> SELECT first_name,last_name, job_id, salary 2 FROM hr.employees 3 WHERE salary = 4 (SELECT MIN(salary) 5 FROM hr.employees); FIRST_NAME LAST_NAME JOB_ID SALARY -------------------- ------------------------- ---------- ---------- TJ Olson ST_CLERK 3100 SQL>
You can use the subqueries with the Having as follows.
List the employees whose salary is greater than the employee who has the minimum salary in the department of 50 as follows.
SQL> SELECT d.department_name,e.department_id, MIN(salary) 2 FROM hr.employees e,hr.departments d where e.department_id=d.department_id 3 GROUP BY e.department_id,d.department_name 4 HAVING MIN(salary) > 5 (SELECT MIN(salary) FROM hr.employees 6 WHERE department_id = 50); DEPARTMENT_NAME DEPARTMENT_ID MIN(SALARY) ------------------------------ ------------- ----------- Finance 100 7900 Public Relations 70 11000 Purchasing 30 3500 Executive 90 23000 Administration 10 5400 Accounting 110 9300 Human Resources 40 7500 Marketing 20 7000 IT 60 5200 Sales 80 7100 10 rows selected. SQL>
You can use the subqueries with In, Any and All operator as follows.
For example; List the emloyees and their salaries whose salary is equal to minimum salary of each department as follows.
SQL>
SQL> SELECT employee_id, first_name,last_name,salary
2 FROM hr.employees
3 WHERE salary in
4 (SELECT MIN(salary)
5 FROM hr.employees
6 GROUP BY department_id);
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
101 Neena Kochhar 23000
102 Lex De Haan 23000
104 Bruce Ernst 7000
107 Diana Lorentz 5200
113 Luis Popp 7900
119 Karen Colmenares 3500
123 Shanta Vollman 7500
131 James Marlow 3500
132 TJ Olson 3100
140 Joshua Patel 3500
144 Peter Vargas 3500
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
150 Peter Tucker 11000
155 Oliver Tuvault 8000
156 Janette King 11000
161 Sarath Sewall 8000
169 Harrison Bloom 11000
173 Sundita Kumar 7100
178 Kimberely Grant 8000
182 Martha Sullivan 3500
184 Nandita Sarchand 5200
191 Randall Perkins 3500
200 Jennifer Whalen 5400
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
202 Pat Fay 7000
203 Susan Mavris 7500
204 Hermann Baer 11000
206 William Gietz 9300
26 rows selected.
SQL>
List the employees whose salary is greater than the minimum salary of employee in IT_PROG department.
SQL> SELECT employee_id, last_name, job_id, salary
2 FROM hr.employees
3 WHERE salary > ANY
4 (SELECT salary
5 FROM hr.employees
6 WHERE job_id = 'IT_PROG')
7 AND job_id <> 'IT_PROG';
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
101 Kochhar AD_VP 23000
102 De Haan AD_VP 23000
108 Greenberg FI_MGR 13008
109 Faviet FI_ACCOUNT 10000
110 Chen FI_ACCOUNT 9200
111 Sciarra FI_ACCOUNT 8700
112 Urman FI_ACCOUNT 8800
113 Popp FI_ACCOUNT 7900
114 Raphaely PU_MAN 12000
120 Weiss ST_MAN 9000
121 Fripp ST_MAN 9200
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
122 Kaufling ST_MAN 8900
123 Vollman ST_MAN 7500
124 Mourgos ST_MAN 6800
145 Russell SA_MAN 15000
146 Partners SA_MAN 14500
147 Errazuriz SA_MAN 13000
148 Cambrault SA_MAN 12000
149 Zlotkey SA_MAN 11500
150 Tucker SA_REP 11000
151 Bernstein SA_REP 10500
152 Hall SA_REP 10000
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
153 Olsen SA_REP 9000
154 Cambrault SA_REP 8500
155 Tuvault SA_REP 8000
156 King SA_REP 11000
157 Sully SA_REP 10500
158 McEwen SA_REP 10000
159 Smith SA_REP 9000
160 Doran SA_REP 8500
161 Sewall SA_REP 8000
162 Vishney SA_REP 11500
163 Greene SA_REP 10500
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
164 Marvins SA_REP 8200
165 Lee SA_REP 7800
166 Ande SA_REP 7400
167 Banda SA_REP 7200
168 Ozer SA_REP 12500
169 Bloom SA_REP 11000
170 Fox SA_REP 10600
171 Smith SA_REP 8400
172 Bates SA_REP 8300
173 Kumar SA_REP 7100
174 Abel SA_REP 12000
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
175 Hutton SA_REP 9800
176 Taylor SA_REP 9600
177 Livingston SA_REP 9400
178 Grant SA_REP 8000
179 Johnson SA_REP 7200
200 Whalen AD_ASST 5400
201 Hartstein MK_MAN 14000
202 Fay MK_REP 7000
203 Mavris HR_REP 7500
204 Baer PR_REP 11000
205 Higgins AC_MGR 13008
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
206 Gietz AC_ACCOUNT 9300
56 rows selected.
SQL>
List the employees whose salary is lower than the employees who is salary is lower than employee in IT_PROG department as follows.
SQL> SELECT employee_id, first_name, last_name, job_id, salary
2 FROM hr.employees
3 WHERE salary < ALL
4 (SELECT salary FROM hr.employees
5 WHERE job_id = 'IT_PROG')
6 AND job_id <> 'IT_PROG';
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID SALARY
----------- -------------------- ------------------------- ---------- ----------
185 Alexis Bull SH_CLERK 5100
192 Sarah Bell SH_CLERK 5000
193 Britney Everett SH_CLERK 4900
188 Kelly Chung SH_CLERK 4800
137 Renske Ladwig ST_CLERK 4600
189 Jennifer Dilly SH_CLERK 4600
141 Trenna Rajs ST_CLERK 4500
186 Julia Dellinger SH_CLERK 4400
133 Jason Mallin ST_CLERK 4300
129 Laura Bissot ST_CLERK 4300
194 Samuel McCain SH_CLERK 4200
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID SALARY
----------- -------------------- ------------------------- ---------- ----------
138 Stephen Stiles ST_CLERK 4200
125 Julia Nayer ST_CLERK 4200
180 Winston Taylor SH_CLERK 4200
142 Curtis Davies ST_CLERK 4100
115 Alexander Khoo PU_CLERK 4100
196 Alana Walsh SH_CLERK 4100
181 Jean Fleaur SH_CLERK 4100
187 Anthony Cabrio SH_CLERK 4000
197 Kevin Feeney SH_CLERK 4000
134 Michael Rogers ST_CLERK 3900
116 Shelli Baida PU_CLERK 3900
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID SALARY
----------- -------------------- ------------------------- ---------- ----------
190 Timothy Gates SH_CLERK 3900
130 Mozhe Atkinson ST_CLERK 3800
117 Sigal Tobias PU_CLERK 3800
195 Vance Jones SH_CLERK 3800
183 Girard Geoni SH_CLERK 3800
126 Irene Mikkilineni ST_CLERK 3700
139 John Seo ST_CLERK 3700
199 Douglas Grant SH_CLERK 3600
118 Guy Himuro PU_CLERK 3600
198 Donald OConnell SH_CLERK 3600
143 Randall Matos ST_CLERK 3600
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID SALARY
----------- -------------------- ------------------------- ---------- ----------
140 Joshua Patel ST_CLERK 3500
144 Peter Vargas ST_CLERK 3500
131 James Marlow ST_CLERK 3500
191 Randall Perkins SH_CLERK 3500
182 Martha Sullivan SH_CLERK 3500
119 Karen Colmenares PU_CLERK 3500
135 Ki Gee ST_CLERK 3400
127 James Landry ST_CLERK 3400
136 Hazel Philtanker ST_CLERK 3200
128 Steven Markle ST_CLERK 3200
132 TJ Olson ST_CLERK 3100
44 rows selected.
SQL>
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