B.4. SUBQUERIES
A subquery is a SELECT statement inside parentheses within another DML statement. It can be in INSERT, UPDATE, DELETE or SELECT statement. It is always enclosed in parentheses (). The ability to use a query within a query was the original reason for the word structured in the name Structured Query Language.[1]
[1] Groff 1999, p. 218.
The result of a SELECT is a virtual table called a result set.
As shown in Table B-5, there are four result set sizes which are useful when discussing subqueries.
| 1. INSERT INTO table SELECT ... | -- target table must exist |
| Subquery Result Set Dimensions | Result Set Name | Example |
|---|---|---|
| 1 X 1 | Scalar | ( SELECT AVG(salary) FROM emps3 ) ( SELECT SUM(order_amt) FROM orders WHERE custid = 11) |
Get Transact-SQL Desk Reference now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
