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.
Subquery types |
Result sets |
Clauses |
|---|---|---|
| Scalar subquery | Single column and single row |
|
| Column subquery | Single column and multiple rows |
|
| Row subqueries | Multiple columns and multiple rows |
|
| Table subquery | Multiple rows and multiple columns |
|
Scenarios
Subqueries are mainly used in the following scenarios:
Define a row set to be inserted into the target table in the
INSERTorCREATE TABLEstatement.Define a row set to be included in a view in the
CREATE VIEWstatement.Define one or more values to be assigned to existing rows in the
UPDATEstatement.Provide values for the
WHERE,HAVING, orSTART WITHclauses in theSELECT,UPDATE, orDELETEstatements.
Keywords in subqueries
Subqueries can contain the following keywords: IN, ANY, SOME, and ALL.
The
INkeyword is frequently used in aWHEREexpression to query data in a specified range.The
ANYandSOMEkeywords 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
ALLkeyword 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 INkeyword is used and the column value in a subquery isNULL, the result of the outer query is empty.
Examples
Create tables named emp and dept and insert proper data to the tables.
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)
);
CREATE TABLE dept(
deptno NUMBER(2,0),
dname VARCHAR(14),
location VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY(deptno)
);
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);
A scalar subquery 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
A table subquery 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> SELECTt1.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
Example 1: A scalar subquery
/*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
Example 2: A column subquery that returns 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, sal Salary FROM emp WHERE sal < ALL (SELECT DISTINCT sal
FROM emp WHERE job = 'MANAGER') AND job!= 'MANAGER';
+--------------+--------------+----------+---------+
| Employee name | Employee ID | Job | 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, sal Salary FROM emp WHERE sal < ALL (SELECT min(sal)
FROM emp WHERE job = 'MANAGER') AND job!= 'MANAGER';
+--------------+--------------+----------+---------+
| Employee name | Employee ID | Job | Salary |
+--------------+--------------+----------+---------+
| SMITH | 1369 | CLERK | 800.00 |
| ALLEN | 1499 | SALESMAN | 1600.00 |
+--------------+--------------+----------+---------+
2 rows in set
Example 3: A row subquery that returns 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 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.
Examples:
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