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> [ <order by clause> ] )
Usage
A subquery is a query that is executed as part of another query expression.
Notes
§
|
Subqueries shall be enclosed in parentheses.
|
§
|
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 not greater than one. If the cardinality is 0, then the value of the scalar subquery is the null value.
|
§
|
Row subqueries shall have a degree greater than one and a cardinality not greater than one. If the cardinality is 0, then all fields of the row subquery have the null value.
|
§
|
Table subqueries are commonly used in predicates, but can also appear in the FROM clause to specify a derived table.
|
§
|
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
NexusDB extensions
|
-
-
|
Core SQL
Support for the ORDER BY clause in <subquery>
|
|
