Subqueries
Wed, 03/04/2009 - 20:02 | 
hdanzl
Specify a scalar value, a row or a table based on a query expression.
Syntax
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> |
Tags:
Questions? Problems?
- Interested to buy but something put you off?
- Can't find what you're looking for?
- Have a suggestion?
Let us know. If you're having trouble contacting us, please check here.
![[]](/support/modules/ubercart/uc_cart/images/bullet-arrow-up.gif "Expand cart block."){kind=icon}
| 7x | SLA - Silver (add years in quantity field) | AUD 6,300.00 |
| 1x | NexusDB AWE DLL (1 installation; binary) | AUD 330.00 |
| 1x | The Full Monty Pack | AUD 1,800.00 |
| 1x | NexusDB Professional Edition | AUD 1,400.00 |
| 2x | Memory Manager V4 | AUD 702.00 |
| 4x | SLA - Gold (add years in quantity field) | AUD 10,666.68 |
| 1x | Visual Studio Standard Pack | AUD 500.00 |
| 2x | NexusDB AWE DLL (site license; binary) | AUD 4,600.00 |
| 1x | Nexus Quality Suite v1 | AUD 550.00 |
| 4x | SLA - Platinum (add years in quantity field) | AUD 16,000.00 |
| 1x | NexusDB Embedded Source | AUD 430.00 |
| 1x | NexusDB Server License Plugin | AUD 250.00 |
| 1x | NexusDB Starter Edition DCU | AUD 150.00 |
| 1x | NexusDB AWE DLL (10 installations; binary) | AUD 1,950.00 |
| 28 Items | Total: AUD 45,628.68 |
| (View cart) (Checkout) | |
Site Updates
- Dec 4 2024 - 1:53pm
- Dec 4 2024 - 1:48pm
- Nov 19 2024 - 1:04pm
- Oct 17 2024 - 10:27am
- Jul 24 2024 - 1:22pm