Subqueries

A subquery is a query nested in an upper-layer query. The SQL language supports multi-level nested queries, which means that one subquery can be nested with other subqueries.

Overview

A subquery is a query nested within another SELECT statement and can return one row, multiple rows, or no result. A SELECT statement can be nested with one or more other SELECT statements. A subquery in the FROM clause of a SELECT statement is also called an inner join view. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery.

Subquery types

Subqueries can be divided into the following types based on the row and column numbers in the result set.

Scenarios

Subqueries are mainly used in the following scenarios:

• Define a row set to be inserted into the specified table in the INSERT or CREATE TABLE statement.

• Define a row set to be included in a view in the CREATE VIEW statement.

• Define one or more values to be assigned to existing rows in the UPDATE statement.

• Provide values for the WHERE, HAVING, or START WITH clause in the SELECT, UPDATE, or DELETE statement.

Keywords in subqueries

Subqueries can contain the following keywords: IN, ANY, SOME, and ALL.

• The IN keyword is frequently used in a WHERE expression to query data in a specified range.

• The ANY and SOME keywords can be used with operators such as =, >, >=, <, <=, and <> to query data equal to, greater than, greater than or equal to, less than, less than or equal to, or not equal to the specified data.

• The ALL keyword can be used with operators such as =, >, >=, <, <=, and <> to specify values that are equal to, greater than, greater than or equal to, less than, less than or equal to, or not equal to all the specified data.

  Notice

  If the NOT IN keyword is used, and the column value in a subquery is NULL, the result of the outer query is empty.

Examples

Create tables named emp_ny, dept_ny, and job_grades_ny, and insert data into them.

1. Create a table named dept_ny.

   obclient> CREATE TABLE dept_ny(
              deptno           NUMBER(2,0),
              dname            VARCHAR(14),
              location         VARCHAR(13),
              CONSTRAINT pk_dept PRIMARY KEY(deptno)
             );
   Query OK, 0 rows affected
   

2. Create a table named job_grades_ny.

   obclient> CREATE TABLE job_grades_ny
             (
             grade_level VARCHAR(5),
             lowest_sal NUMBER,
             highest_sal NUMBER);
   Query OK, 0 rows affected
   

3. Create a table named emp_ny.

   obclient> CREATE TABLE emp_ny(
              empno         NUMBER(4,0),
              empname       VARCHAR(10),
              job           VARCHAR(9),
              mgr           NUMBER(4,0),
              hiredate      DATE,
              sal           NUMBER(7,2),
              comm          NUMBER(7,2),
              deptno        NUMBER(2,0),
              age           NUMBER(2,0),
              CONSTRAINT PK_emp PRIMARY KEY (empno),
              CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept_ny (deptno)
              );
   Query OK, 0 rows affected
   

4. Insert data into the dept_ny table.

   obclient> INSERT INTO dept_ny VALUES
             (20,'Finance','beijing'),
             (35,'Administration','hangzhou'),
             (40,'Development','xian'),
             (30,'Workshop','guangzhou'),
             (25,'Legal affairs','shanghai'),
             (45,'Office','suzhou');
   Query OK, 6 rows affected
   Records: 6  Duplicates: 0  Warnings: 0
   

5. Insert data into the job_grades_ny table.

   obclient> INSERT INTO job_grades_ny VALUES ('A', 1000, 1999),
             ('B', 2000, 2999),('C', 3000, 3999),('D', 40000, 4999),
             ('E', 5000, 5999);
   Query OK, 5 rows affected
   Records: 5  Duplicates: 0  Warnings: 0
   

6. Insert data into the emp_ny table.

   obclient> INSERT INTO emp_ny VALUES
               (1369,'SMITH','CLERK',1902,DATE'1980-12-17',800.00,NULL,20,22),
               (1499,'ALLEN','SALESMAN',1698,DATE'1981-02-20',1600.00,300.00,35,22),
               (1566,'JONES','MANAGER',1839,DATE'1981-04-02',2975.00, NULL,40,22),
               (1698,'BLAKE' ,'MANAGER',1839,DATE'1981-05-01',2850.00,NULL ,30,33),
               (1788,'SCOTT','ANALYST',1566,DATE'1987-07-15',3000.00,NULL ,25,33),
               (1902,'FORD','ANALYST',1566,DATE'1981-12-05',3000.00, NULL,45,22);
   Query OK, 6 rows affected
   Records: 6  Duplicates: 0  Warnings: 0
   

Example 1: Scalar subqueries with a SELECT clause

• Use a scalar subquery to obtain the number of employees in each department.

  obclient> SELECT a.*,(SELECT count(*) FROM emp_ny b WHERE b.deptno = a.deptno) AS Number of employees FROM dept_ny a;
  +--------+----------------+-----------+---------------------+
  | DEPTNO | DNAME          | LOCATION  | Number of employees |
  +--------+----------------+-----------+---------------------+
  |     20 | Finance        | beijing   |                   1 |
  |     25 | Legal affairs  | shanghai  |                   1 |
  |     30 | Workshop       | guangzhou |                   1 |
  |     35 | Administration | hangzhou  |                   1 |
  |     40 | Development    | xian      |                   1 |
  |     45 | Office         | suzhou    |                   1 |
  +--------+----------------+-----------+---------------------+
  6 rows in set
  

• Use a scalar subquery to obtain the department that meets the condition empno =1566.

  obclient> SELECT (SELECT a.dname FROM dept_ny a, emp_ny b WHERE a.deptno = b.deptno AND b.empno = 1566) AS Department name FROM emp_ny b;
  +-----------------+
  | Department name |
  +-----------------+
  |   Development   |
  |   Development   |
  |   Development   |
  |   Development   |
  |   Development   |
  |   Development   |
  +-----------------+
  6 rows in set
  

Example 2: Subqueries with WHERE and HAVING clauses

• Scalar subqueries

  Use a subquery with the WHERE and HAVING clauses to obtain the ID and minimum salary of departments whose minimum salary is greater than that of Department No.30.

  obclient> SELECT min(a.sal) minsalary,deptno FROM emp_ny a GROUP BY a.deptno
           HAVING min(a.sal) > (SELECT min(sal) FROM emp_ny WHERE deptno = 30);
  +-----------+--------+
  | MINSALARY | DEPTNO |
  +-----------+--------+
  |      3000 |     25 |
  |      2975 |     40 |
  |      3000 |     45 |
  +-----------+--------+
  3 rows in set
  

• Column subqueries that return a result set with one column and multiple rows

  Use a column subquery to obtain the employee ID, employee name, job title, and salary of employees whose job is not MANAGER and whose salary is lower than that of all managers.

  obclient> SELECT empname Employee name, empno Employee ID, job Job title, sal Salary FROM emp_ny
           WHERE sal < ALL
           (SELECT DISTINCT sal FROM emp_ny WHERE job = 'MANAGER') AND job!= 'MANAGER';
  +---------------+-------------+-----------+--------+
  | Employee name | Employee ID | Job title | Salary |
  +---------------+-------------+-----------+--------+
  | SMITH         |        1369 | CLERK     |    800 |
  | ALLEN         |        1499 | SALESMAN  |   1600 |
  +---------------+-------------+-----------+--------+
  2 rows in set
  

  Use a column subquery with the aggregate function MIN() to obtain the employee ID, employee name, job title, and salary of the employees whose job is not MANAGER and whose salary is lower than that of all managers.

  obclient> SELECT empname Employee name, empno Employee ID, job Job title, sal Salary FROM emp_ny
           WHERE sal < ALL (SELECT min(sal) FROM emp_ny WHERE job = 'MANAGER') AND job!= 'MANAGER';
  +---------------+-------------+-----------+--------+
  | Employee name | Employee ID | Job title | Salary |
  +---------------+-------------+-----------+--------+
  | SMITH         |        1369 | CLERK     |    800 |
  | ALLEN         |        1499 | SALESMAN  |   1600 |
  +---------------+-------------+-----------+--------+
  2 rows in set
  

• Row subqueries that return a result set with one row and multiple columns

  Use a row subquery to obtain the employee with the largest empno value and the highest salary.

  obclient> SELECT * FROM emp_ny a
           WHERE a.empno = (SELECT max(empno) FROM emp_ny)
           AND sal = (SELECT max(sal) FROM emp_ny);
  +-------+---------+---------+------+-----------+------+------+--------+------+
  | EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
  +-------+---------+---------+------+-----------+------+------+--------+------+
  |  1902 | FORD    | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
  +-------+---------+---------+------+-----------+------+------+--------+------+
  1 row in set
  

Example 3: EXISTS and correlated subqueries

In an EXISTS subquery, the main query is first executed to obtain a result, and then the result is filtered based on the conditions in the subquery. The subquery contains the fields that are used in the main query. Therefore, the subquery is called a related subquery. The query result of EXISTS is 1 or 0, which indicates whether the result set of the subquery contains values. In most cases, an EXISTS subquery can be replaced with an IN clause.

• Use an EXISTS clause to query the DNAME values in the dept_ny table whose deptno values can be found in the emp_ny table.

  obclient> SELECT dname FROM dept_ny a WHERE EXISTS(SELECT 1 FROM emp_ny b WHERE a.deptno = b.deptno);
  +----------------+
  | DNAME          |
  +----------------+
  | Finance        |
  | Legal affairs  |
  | Workshop       |
  | Administration |
  | Development    |
  | Office         |
  +----------------+
  6 rows in set
  

• Use the keyword IN to query the DNAME values in the dept_ny table whose deptno values can be found in the emp_ny table.

  obclient> SELECT dname FROM dept_ny a WHERE a.deptno IN (SELECT deptno FROM emp_ny);
  +----------------+
  | DNAME          |
  +----------------+
  | Finance        |
  | Legal affairs  |
  | Workshop       |
  | Administration |
  | Development    |
  | Office         |
  +----------------+
  6 rows in set
  

Example 4: MULTISET subqueries

The result of a CAST(MULTISET (subquery)) AS type_name subquery is of the SET type. The rows generated by the subquery are replaced with corresponding set elements.

obclient [USER001]> CREATE TYPE cust_tbl2 AS TABLE OF varchar2(20);
Query OK, 0 rows affected

obclient [USER001]> CREATE TABLE tbl1(col1 INT, col2 INT);
Query OK, 0 rows affected

obclient [USER001]> CREATE TABLE tbl2(col1 INT, col2 INT);
Query OK, 0 rows affected

obclient [USER001]> INSERT INTO tbl1 VALUES (1,1);
Query OK, 1 row affected

obclient [USER001]> INSERT INTO tbl2 VALUES (1,2),(1,3);
Query OK, 2 rows affected
 Records: 2  Duplicates: 0  Warnings: 0

obclient [USER001]> SELECT * FROM tbl1,TABLE(CAST(MULTISET(SELECT tbl2.col2 FROM tbl2 WHERE tbl1.col1=tbl2.col1) AS cust_tbl2));
+------+------+--------------+
| COL1 | COL2 | COLUMN_VALUE |
+------+------+--------------+
|    1 |    1 | 2            |
|    1 |    1 | 3            |
+------+------+--------------+
2 rows in set

References

• SELECT
• CAST
• CREATE TYPE