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

Subqueries can be contained in various clauses of SQL statements, such as SELECT, FROM, and WHERE. A subquery in the FROM clause of a SELECT statement is called an inner join view. You can nest any number of subqueries in an inner join view. A subquery in the WHERE clause of a SELECT statement is 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 and dept and insert proper data into the tables.

CREATE TABLE dept(  
    deptno           NUMBER(2,0),  
    dname            VARCHAR(14),  
    location         VARCHAR(13),   
    CONSTRAINT pk_dept PRIMARY KEY(deptno)  
);

INSERT INTO dept VALUES (20,'ACCOUNTING','Los Angeles');
INSERT INTO dept VALUES (30,'OPERATIONS','CHICAGO');
INSERT INTO dept VALUES (40,'SALES','NEW YORK');

CREATE TABLE emp(  
    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),   
    CONSTRAINT PK_emp PRIMARY KEY (empno),
    CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)  
);

INSERT INTO emp VALUES (1698,'BLAKE','MANAGER',1839,'1981-5-1',2850,null,30);
INSERT INTO emp VALUES (1566,'JONES','MANAGER',1839, '1981-4-2',2975,null,40);
INSERT INTO emp VALUES (1788,'SCOTT','ANALYST',1566, '1987-7-15',3000,null,20);
INSERT INTO emp VALUES (1902,'FORD','ANALYST',1566, '1981-12-5',3000,null,40);
INSERT INTO emp VALUES (1369,'SMITH','CLERK',1902, '1980-12-17',800,null,20);
INSERT INTO emp VALUES (1499,'ALLEN','SALESMAN',1698, '1981-2-20',1600,300,30);

CREATE TABLE job_grades
(
     grade_level VARCHAR(5),
     lowest_sal INT,
     highest_sal INT);

INSERT INTO job_grades VALUE ('A', 1000, 1999);
INSERT INTO job_grades VALUE ('B', 2000, 2999);
INSERT INTO job_grades VALUE ('C', 3000, 3999);
INSERT INTO job_grades VALUE ('D', 40000, 4999);
INSERT INTO job_grades VALUE ('E', 5000, 5999);

Scalar subqueries with a SELECT clause

obclient> SELECT a.*,(SELECT count(*) FROM emp b WHERE b.deptno = a.deptno) AS Number of employees FROM dept a;
+--------+------------+-------------+---------------------+
| deptno | dname      | location    | Number of employees |
+--------+------------+-------------+---------------------+
|     20 | ACCOUNTING | Los Angeles |                   2 |
|     30 | OPERATIONS | CHICAGO     |                   2 |
|     40 | SALES      | NEW YORK    |                   2 |
+--------+------------+-------------+---------------------+
3 rows in set

obclient> SELECT (SELECT a.dname FROM dept a, emp b WHERE a.deptno = b.deptno AND b.empno = 1566) AS Department name;
+--------------+
| Department name      |
+--------------+
| SALES        |
+--------------+
1 row in set

Table subqueries with a FROM clause

Use the result set of a subquery as a table and give an alias to the table. For example, the aliases of the tables in the following sample code are t1 and t2.

obclient> SELECT t1.deptno,sa AS 'Average salary', t2.grade_level FROM (SELECT deptno,avg(a.sal) sa FROM emp a GROUP BY a.deptno) t1, job_grades t2 WHERE t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
+--------+----------------+-------------+
| deptno | Average salary | grade_level |
+--------+----------------+-------------+
|     20 |    1900.000000 | A           |
|     30 |    2225.000000 | B           |
|     40 |    2987.500000 | B           |
+--------+----------------+-------------+
3 rows in set

Subqueries with WHERE and HAVING clauses

• Scalar subqueries

  /*Query the IDs of departments whose minimum salary is greater than that of No.30 department and their minimum salaries.*/
  obclient> SELECT min(a.sal) minsalary,deptno FROM emp a GROUP BY a.deptno HAVING min(a.sal) > (SELECT min(sal) FROM emp WHERE deptno = 30);
  +-----------+--------+
  | minsalary | deptno |
  +-----------+--------+
  |   2975.00 |     40 |
  +-----------+--------+
  1 row in set
  

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

  /*Return the employee ID, employee name, job, and salary of the employees whose total salary is lower than that of the manager.*/
  
  obclient> SELECT empname Employee name, empno Employee ID, job Job title, sal Salary FROM emp WHERE sal < ALL (SELECT DISTINCT sal
  FROM emp WHERE job = 'MANAGER') AND job!= 'MANAGER';
  +---------------+-------------+-----------+---------+
  | Employee name | Employee ID | Job title | Salary  |
  +---------------+-------------+-----------+---------+
  | SMITH         |        1369 | CLERK     |  800.00 |
  | ALLEN         |        1499 | SALESMAN  | 1600.00 |
  +---------------+-------------+-----------+---------+
  2 rows in set
  
  obclient> SELECT empname Employee name, empno Employee ID, job Job title, sal Salary FROM emp WHERE sal < ALL (SELECT min(sal)
      FROM emp WHERE job = 'MANAGER') AND job!= 'MANAGER';
  +---------------+-------------+-----------+---------+
  | Employee name | Employee ID | Job title | Salary  |
  +---------------+-------------+-----------+---------+
  | SMITH         |        1369 | CLERK     |  800.00 |
  | ALLEN         |        1499 | SALESMAN  | 1600.00 |
  +---------------+-------------+-----------+---------+
  2 rows in set
  

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

  obclient> SELECT * FROM emp a WHERE a.empno = (SELECT max(empno) FROM emp) AND sal = (SELECT max(sal) FROM emp);
  +-------+---------+---------+------+------------+---------+------+--------+
  | empno | empname | job     | mgr  | hiredate   | sal     | comm | deptno |
  +-------+---------+---------+------+------------+---------+------+--------+
  |  1902 | FORD    | ANALYST | 1566 | 1981-12-05 | 3000.00 | NULL |     40 |
  +-------+---------+---------+------+------------+---------+------+--------+
  1 row in set
  

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 correlated 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.

Here is an example:

obclient> SELECT exists(SELECT empno FROM emp WHERE sal = 3000) AS 'EXISTS returns 1 or 0';
+----------------------+
| EXISTS returns 1 or 0     |
+----------------------+
|                    1 |
+----------------------+
1 row in set

obclient> SELECT dname FROM dept a WHERE exists(SELECT 1 FROM emp b WHERE a.deptno = b.deptno);
+------------+
| dname      |
+------------+
| ACCOUNTING |
| OPERATIONS |
| SALES      |
+------------+
3 rows in set

obclient> SELECT dname FROM dept a WHERE a.deptno IN (SELECT deptno FROM emp);
+------------+
| dname      |
+------------+
| ACCOUNTING |
| OPERATIONS |
| SALES      |
+------------+
3 rows in set