A subquery is a query nested within an outer query. The SQL language supports multiple levels of nested queries, meaning a subquery can contain other subqueries.
Introduction to subqueries
Subqueries 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 within an inline view. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery.
Types of subqueries
Based on the number of rows and columns in the result set, subqueries can be classified into the following four types:
Subquery type |
Result set |
Related clause |
|---|---|---|
| Scalar subquery | Single column, single row |
|
| Column subquery | Single column, multiple rows |
|
| Row subquery | Multiple columns, multiple rows |
|
| Table subquery | Multiple rows, multiple columns |
|
Application scenarios of subqueries
Subqueries are mainly used in the following scenarios:
To define the row set for the target table to be inserted into in
INSERTorCREATE TABLEstatements.To define the row set to include in a view in a
CREATE VIEWstatement.To define one or more values to assign to existing rows in an
UPDATEstatement.To provide values for conditions in the
WHERE,HAVING, orSTART WITHclauses ofSELECT,UPDATE, andDELETEstatements.
Keywords in subqueries
Keywords in subqueries include IN, ANY, SOME, and ALL.
INis commonly used inWHEREexpressions to query data within a specific range.ANYandSOMEcan be used with operators such as=,>,>=,<,<=, and<>to mean equal to, greater than, greater than or equal to, less than, less than or equal to, or not equal to any one of the values.ALLcan be used with operators such as=,>,>=,<,<=, and<>to mean equal to, greater than, greater than or equal to, less than, less than or equal to, or not equal to all of the values.Note
When using
NOT IN, if a column value in the subquery isNULL, the result of the outer query is empty.
Subquery examples
Create 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);
Example of a SELECT clause with a scalar subquery
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
Example of FROM clause and table subquery
To use the result set of a subquery as a table, you must assign it an alias. In the following examples, the tables are named 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
Example of subqueries with WHERE and HAVING clauses
Scalar subquery
/*Query the department ID and its minimum wage that are greater than the minimum wage of Department No. 30.*/ 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 setColumn subqueries return a result set with one column and multiple rows.
/*Return the employee ID, name, position, and salary of employees whose salaries are lower than that of job with the role MANAGER among all other jobs.*/ obclient> SELECT empname AS "Employee Name", empno AS "Employee ID", job AS "Position", sal AS "Salary" FROM emp WHERE sal < ALL (SELECT DISTINCT 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 obclient> SELECT empname AS "Employee Name", empno AS "Employee ID", job AS "Position", sal AS "Salary" 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 setRow subqueries return a result set of 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
The EXISTS subquery executes the main query first to obtain its result, and then filters the data based on the result of the subquery. The subquery references fields used in the main query, so it is called a correlated subquery. The result of an EXISTS query is either 1 or 0, indicating whether the result set of the subquery contains any values. Generally, an EXISTS subquery can be replaced with an IN statement.
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