JetSQL » Statements » SELECT SUBQUERY
- Syntax:
-
SELECT select_statement
(SELECT select_statement
(SELECT select_statement
( ... ) ) ) ) - select_statement
- The remaining portion of the SELECT statement.
When a SELECT statement is nested inside of a DELETE, INSERT, SELECT, SELECT INTO, or an UPDATE statement, it is referred to as a subquery clause.
There are three reserved words, ALL, ANY, and SOME, that can be used in subqueries to make comparisons. However, Microsoft states that ALL and SOME are synonymous.
Examples
Code:
SELECT * FROM MediCarePatients WHERE ssn IN
(SELECT ssn FROM DentalIns);
Output:
SSN
FullName
Address
City
State
PhoneNumber
123-45-6789
Jared Johnson
2020 Lakeview Dr.
Memphis
TN
(555) 555-5141
987-65-4321
Richard Danson
127 Leeland Heights
Chattanooga
TN
(555) 555-3124
111-22-3333
Tammy Harper
P.O. Box 5812
Powell
TN
(555) 555-2930
576-29-0012
Herbert Gaddis
P.O. Box 2281
Nashville
TN
(555) 555-0091
(4 row(s) affected)
Explanation:
In this example we create a table of Medicare patients who also have dental insurance. The social security number, ssn, is used as a key to compare the two tables, 'MediCarePatients' and 'DentalIns'.
Language(s): MS SQL Server
Code:
SELECT * FROM MediCarePatients WHERE ssn NOT IN
(SELECT ssn FROM DentalIns);
Output:
SSN
FullName
Address
City
State
PhoneNumber
313-66-2001
Robert Davis
2812 Main St.
Cleveland
TN
(555) 555-0921
999-99-9999
Steven Raper
31 Belleview Rd.
Murfreesboro
TN
(555) 555-1812
488-48-8484
Jack Swanson
P.O. Box 3713
Nashville
TN
(555) 555-1276
(3 row(s) affected)
Explanation:
This query uses a NOT to create a table of Medicare patients who do not have dental insurance.
Language(s): MS SQL Server
Code:
SELECT AvgEggSize FROM BillsBirdNestList WHERE AvgEggSize < ALL
(SELECT AvgEggSize FROM LindasBirdNestList);
Output:
AvgEggSize 1.5 2.0 2.3
(3 row(s) affected)
Explanation:
The "< ALL" comparison will create a list of AvgEggSize from BillsBirdNestList that contains only those entries that are smaller than the smallest AvgEggSize value in LindasBirdNestList.
Language(s): MS SQL Server
Code:
SELECT AvgEggSize FROM BillsBirdNestList WHERE AvgEggSize < ANY
(SELECT AvgEggSize FROM LindasBirdNestList);
Output:
AvgEggSize 1.5 2.0 2.3 3.2 2.8 4.0
(6 row(s) affected)
Explanation:
In contrast to the "< ALL" comparison, the "< ANY" comparison will create a list of AvgEggSize from BillsBirdNestList that contains only those entries that are smaller than the largest AvgEggSize value in LindasBirdNestList.
Language(s): MS SQL Server
See Also:
