SQL Subquery, Nested Queries and In Any All Operator in Subquery in Oracle SQL | Oracle SQL Tutorials -26

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

About 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 [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *