Subqueries|V3.2.4|OceanBase Database| docs|Distributed Database

Subqueries

Last Updated：2023-10-24 09:23:03 Updated

A subquery is a query nested in another query. The SQL language supports multi-level nested queries, which means that a subquery can be nested in another subquery.

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 number and column number in a result set.

Subquery types	Result set	Clause
Scalar subquery	Single column and single row	`SELECT` `WHERE` `HAVING`
Column subquery	Single column and multiple rows	`WHERE` `HAVING`
Row subquery	Multiple columns and multiple rows	`WHERE` `HAVING`
Table subquery	Multiple rows and multiple columns	`FROM` `EXISTS`

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 commonly 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 parent query is empty.

Examples

Create tables named emp and dept and insert proper data.

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

In an EXISTS subquery, execute the main query to obtain a result and then filter the result 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 is used to check for values in the result set of the subquery. An EXISTS subquery can be replaced with an IN clause.

Sample code:

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