Subqueries

A subquery is a query nested in an upper-level query. SQL allows multiple levels of nested queries, meaning that a subquery can contain other subqueries.

Subqueries

A subquery can appear in various clauses of an SQL statement, such as the SELECT, FROM, and WHERE clauses. A subquery in the FROM clause of a SELECT statement is also called an inline view. You can nest any number of subqueries in an inline view. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery.

Subquery types

Based on the number of rows and columns in the result set, subqueries can be categorized into four types:

Subquery type	Result set	Related clauses
Scalar subquery	Single column and single row	SELECT clause WHERE clause HAVING clause
Column subquery	Single column and multiple rows	WHERE clause HAVING clause
Row subquery	Multiple columns and multiple rows	WHERE clause HAVING clause
Table subquery	Multiple rows and multiple columns	FROM clause EXISTS clause

Subquery scenarios

Subqueries are primarily used in the following scenarios:

• To define the set of rows to be inserted into the target table in an INSERT or CREATE TABLE statement.

• To define the set of rows to be included in a view in a CREATE VIEW statement.

• To define one or more values to be assigned to existing rows in an UPDATE statement.

• To provide values for conditions in the WHERE, HAVING, or START WITH clauses of SELECT, UPDATE, and DELETE statements.

Subquery keywords

The subquery keywords include IN, ANY, SOME, and ALL.

• IN is often used in the WHERE clause to query data within a specific range.

• ANY and SOME can be used with the =, >, >=, <, <=, and <> operators to indicate that the value is equal to, greater than, greater than or equal to, less than, less than or equal to, or not equal to any of the values in the subquery.

• ALL can be used with the =, >, >=, <, <=, and <> operators to indicate that the value is equal to, greater than, greater than or equal to, less than, less than or equal to, or not equal to all of the values in the subquery.

  tip

  When using NOT IN, if the column value in the subquery is NULL, the result of the outer query will be empty.

Subquery examples

Create the sample tables emp and dept, and insert appropriate data.

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);

SELECT clause with scalar subquery example

SELECT a.*,(SELECT count(*) FROM emp b WHERE b.deptno = a.deptno) AS 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

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

Example that uses the FROM clause with a subquery

You must assign an alias to the result set of a subquery, as the following example shows, by specifying the t1 and t2 table aliases.

SELECT t1.deptno, sa AS 'Average salary', t2.grade_level FROM (SELECT deptno, AVG(a.sal) sa FROM emp a GROUP BY deptno) t1, job_grades t2 WHERE t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
+--------+--------------+-------------+
| deptno | Avg_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 subquery

  /*Query for the department number and its minimum salary greater than the department 30 minimum salary*/
  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

• A subquery that returns a column containing multiple rows

  /*Returns the employee number, name, job, and salary of other employees whose salary is less than the salary of MANAGER job.*/
  
  SELECT empname 员工姓名, empno 员工编号, job 职位, sal 薪水 FROM emp WHERE sal < ALL (SELECT DISTINCT sal
  FROM emp WHERE job = 'MANAGER') AND job!= 'MANAGER';
  +--------------+--------------+----------+---------+
  | Name       | ID     | Position  | Salary    |
  +--------------+--------------+----------+---------+
  | SMITH        |         1369 | CLERK    |  800.00 |
  | ALLEN        |         1499 | SALESMAN | 1600.00 |
  +--------------+--------------+----------+---------+
  2 rows in set
  
  SELECT empname, empno, job, sal FROM emp WHERE sal < ALL (SELECT min(sal)
      FROM emp WHERE job = 'MANAGER') AND job!= 'MANAGER';
  +--------------+--------------+----------+---------+
  | Employee Name | Employee ID | Position | Salary |
  +--------------+--------------+----------+---------+
  | SMITH        |         1369 | CLERK    |  800.00 |
  | ALLEN        |         1499 | SALESMAN | 1600.00 |
  +--------------+--------------+----------+---------+
  2 rows in set

• A row subquery that returns a result set with one row and multiple columns

  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

The EXISTS subquery executes the subquery and determines whether the result of the subquery meets a condition in the main query. It is also called a correlated subquery because it involves the fields used in the main query. EXISTS returns 1 or 0 to indicate whether the subquery result set contains values. Generally, you can replace the EXISTS subquery with an IN subquery.

Here is an example:

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

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

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