STATEMENT:  SELECT SUBQUERY
SELECT selectstatement
  (SELECT selectstatement
    (SELECT selectstatement
      ( ... ) ) ) )
 
When a SELECT statement is nested inside of a DELETE, INSERT ... INTO, SELECT, SELECT ... INTO, or a UPDATE statement, it is refered to as a subquery clause.
 
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.
 
SELECT * FROM MediCarePatients
WHERE ssn IN
  (SELECT ssn FROM DentalIns);
 
If we use a NOT, we create a table of Medicare patients who do not have dental insurance:
 
SELECT * FROM MediCarePatients
WHERE ssn NOT IN
  (SELECT ssn FROM DentalIns);
 
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.
 
Consider the two following examples. 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.
 
In contrast, the "< ANY" comparison will create a list of AvgEggSize from BillsBirdNestList that contains only those entries that are smaller that the largest AvgEggSize value in LindasBirdNestList.
 
SELECT AvgEggSize FROM BillsBirdNestList WHERE AvgEggSize < ALL   (SELECT AvgEggSize FROM LindasBirdNestList);
 
SELECT AvgEggSize FROM BillsBirdNestList WHERE AvgEggSize < ANY   (SELECT AvgEggSize FROM LindasBirdNestList);

Copyright 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information