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 nothing. 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 target 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 clauses in the SELECT, UPDATE, or DELETE statements.

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 emp_ny, dept_ny, and job_grades_ny, and insert data to them.

1. Create the dept_ny table.

   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 the job_grades_ny table.

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

3. Create the emp_ny table.

   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 to 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 to 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 to 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
   

A scalar subquery 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

Subqueries with WHERE and HAVING clauses

Example 1: A scalar subquery

Use subqueries with WHERE and HAVING clauses to obtain the IDs of departments whose minimum salary is greater than that of Department No.30 and to obtain the minimum salaries of these departments.

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

Example 2: A column subquery that returns a result set with one column and multiple rows

Use a column subquery to obtain the employee IDs, names, jobs, and salaries of employees whose jobs are not MANAGER and whose total salaries are lower than that of the manager.

obclient> SELECT empname Employee name, empno Employee ID, job Job, 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     | 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 IDs, names, jobs, and salaries of the employees whose jobs are not MANAGER and whose total salaries are lower than that of the manager.

obclient> SELECT empname Employee name, empno Employee ID, job Job, 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     | Salary    |
+--------------+--------------+----------+--------+
| SMITH        |         1369 | CLERK    |    800 |
| ALLEN        |         1499 | SALESMAN |   1600 |
+--------------+--------------+----------+--------+
2 rows in set

Example 3: A row subquery that returns 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

EXISTS and related 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. An EXISTS subquery can be replaced with an IN clause.

Example 1

Use an EXISTS clause to query the DNAME data in the dept_ny table, which has the same deptno values in the emp_ny table as in the dept_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 data in the dept_ny table, which has the same deptno values in the emp_ny table as in the dept_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