DevGuru Jet SQL SELECT SUBQUERY Statement

Quick References

Features

Partners


			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 than 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);