Specify a scalar value, a row or a table based on a query expression.

 

Syntax

 

<scalar subquery> ::= <subquery>

 

<row subquery> ::= <subquery>

 

<table subquery> ::= <subquery>

 

<subquery> ::= ( <query expression> )

 

 

Usage

 

A subquery is a query that is executed as part of another query expression.

 

 

Notes

 

Subqueries shall be enclosed in parentheses.



 

Table subqueries are commonly used in predicates, but can also appear in the FROM clause to specify a derived table.



 

Scalar subqueries can be used anywhere a value expression is allowed, including computed columns in the select list. A scalar subquery shall have a degree of one and a cardinality of one.



 

A correlated subquery is a subquery that is referencing data in an outer query. A subquery specified in the select list is typically correlated with the main query to produce meaningful data. Since correlated subqueries depend on data from the outer query, they must normally be evaluated for each row produced by the outer query.



 

 

Examples

 

1)

The following examples uses a subquery with the IN predicate to select students who are enrolled in courseID 730:



 


SELECT studentID, studentName


FROM students


WHERE studentID IN ( SELECT studentID FROM enrolls WHERE courseID = 730 )



 

2) The following example uses a subquery in the FROM clause instead of a base table:

 


SELECT *


FROM ( SELECT studentID, studentName, gender FROM students ) AS student_list



 

3)

The following example uses a correlated subquery in the select list to count the number of courses related to the selected student:



 


SELECT




courseID,


studentID,


( SELECT COUNT( * ) FROM enrolls WHERE studentID = e.studentID ) AS numCourses




FROM enrolls e


ORDER BY courseID



 

 


 

Conformance

 

SQL:2003 standard

-

Core SQL