T-SQL » 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: