A subquery is a query called from a query or another subquery and is thus an expression. Subqueries can help to break down complexity much like objects do. Subqueries can in some situations be a very useful SQL tuning tool and solution.

Subquery Syntax

SELECT column
              ,(SELECT …)
              ,(SELECT …)
FROM table
WHERE x IN
   (SELECT … FROM … WHERE y IN
      (SELECT …));

Where Subqueries are Used

Subqueries can be used in the following SQL statements and clauses.

SELECT clause.

SELECT 
	(SELECT name FROM author WHERE author_id = co.coauthor_id)
FROM coauthor co;

WHERE clause.

SELECT * FROM coauthor WHERE coauthor_id IN
	(SELECT author_id FROM author);

ORDER BY clause.

SELECT * FROM author
ORDER BY (SELECT 'name' FROM DUAL);

HAVING clause.

SELECT name, COUNT(name) FROM subject
GROUP BY name
HAVING name = ANY (SELECT name FROM subject);

FROM clause.

SELECT COUNT(*)
FROM (SELECT * FROM subject);

INSERT VALUES clause.

INSERT INTO subject(subject_id, name)
VALUES(
	 (SELECT MAX(subject_id)+1 FROM subject)
	,'Temporary');
ROLLBACK;

UPDATE statement SET = ...

UPDATE subject SET parent_id = NULL
WHERE subject_id NOT IN 
	(SELECT subject_id FROM subject
	WHERE parent_id IS NOT NULL);
ROLLBACK;

Function parameter and Case statement expression.

SELECT LPAD('xxx',10,(SELECT * FROM DUAL)) FROM DUAL;

Types of Subqueries

Subqueries can be of the following types.

Single row.
Multiple row.
Multiple column.
Regular.
Correlated.

Single Row Subquery

A single row subquery returns an exact match or a single row. This query contains a subquery returning a single row.

SELECT subject_id FROM subject WHERE name = 'Science Fiction';

SUBJECT_ID
----------
         7

SELECT * FROM publication
WHERE subject_id =
	(SELECT subject_id FROM subject WHERE name = 'Science Fiction');

PUBLICATION_ID SUBJECT_ID  AUTHOR_ID TITLE
-------------- ---------- ---------- -----------------------
             1          7          2 Cities in Flight
             2          7          2 A Case of Conscience
             3          7          3 Foundation
             4          7          3 Second Foundation
             5          7          3 Foundation and Empire
             6          7          3 Foundation's Edge
             7          7          3 Prelude to Foundation
             9          7          4 Lucifer's Hammer
            10          7          4 Footfall
            11          7          4 Ringworld

10 rows selected.

The subquery in this query returns more than one row and causes an error. A single row returning subquery is required to produce an exact match check, and thus a single row.

SELECT subject_id FROM subject WHERE parent_id IS NULL;

SUBJECT_ID
----------
         1
         6

SELECT * FROM publication WHERE subject_id =
	(SELECT subject_id FROM subject WHERE parent_id IS NULL);
(SELECT subject_id
  *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

Multiple Row Subquery

Unlike a single row returning subquery a multiple row subquery is validated by a calling query using a set membership check rather than an exact match; thus the subquery can return one or more rows.

SELECT subject_id FROM publication;

SUBJECT_ID
----------
         7
         7
         7
         7
         7
         7
         7
        15
         7
         7
         7

11 rows selected.

SELECT name FROM subject WHERE subject_id IN
	(SELECT subject_id FROM publication);

NAME
--------------------------------
Science Fiction
Shakespearian

Multiple Column Subquery

A multiple column subquery returns more than one column. Those multiple columns can both be retrieved and tested against.

SELECT author_id, publication_id FROM author
	FULL OUTER JOIN publication USING(author_id)
WHERE (author_id, publication_id) NOT IN
	(SELECT coauthor_id, publication_id FROM coauthor);

 AUTHOR_ID PUBLICATION_ID
---------- --------------
         2              1
         2              2
         3              3
         3              4
         3              5
         3              6
         3              7
         6              8
         4              9
         4             10
         4             11
         1

12 rows selected.

Regular Subquery

A regular subquery is non-correlated and does not establish any type of direct relationship between the calling query and the subquery.

SELECT name FROM subject
WHERE subject_id IN
	(SELECT subject_id FROM subject WHERE name LIKE '%a%');

NAME
--------------------------------
Metaphysics
Fantasy
Drama
Literature
Victorian
Shakespearian
Modern American
19th Century American

8 rows selected.

The IN comparison condition should be used for literal values only.

SELECT name FROM subject WHERE subject_id IN (1,2,3,4,5);

NAME
--------------------------------
Computers
Metaphysics
Esoteric
Self Help
Non-Fiction

Correlated Subquery

A correlated subquery establishes a correlation between calling query and subquery such that there is a distinct relationship between calling query and subquery. The calling query passes one or more values into the subquery on a row by row basis where the subquery uses passed values for execution of the subquery.

SELECT s.name FROM subject s
WHERE EXISTS
	(SELECT subject_id FROM publication WHERE subject_id = s.subject_id);

NAME
--------------------------------
Science Fiction
Shakespearian

The following query passes two values into the subquery.

SELECT a.name, p.title FROM author a
	FULL OUTER JOIN publication p ON (p.author_id = a.author_id)
WHERE EXISTS
	(SELECT * FROM coauthor WHERE coauthor_id = a.author_id 
	OR publication_id = p.publication_id);

NAME                             TITLE
-------------------------------- ----------------------------------------
Larry Niven                      Lucifer's Hammer
Larry Niven                      Footfall
Jerry Pournelle