Use the IN predicate to retrieve only those records in the main query for which some record in the subquery contains an equal value. If you changed ANY to ALL in the previous example, the query would return only those products whose unit price is greater than that of all products sold at a discount of 25 percent or more. Use the ALL predicate to retrieve only those records in the main query that satisfy the comparison with all records retrieved in the subquery. The following example returns all products whose unit price is greater than that of any product sold at a discount of 25 percent or more: SELECT * FROM Products Use the ANY or SOME predicate, which are synonymous, to retrieve records in the main query that satisfy the comparison with any records retrieved in the subquery.
In a subquery, you use a SELECT statement to provide a set of one or more specific values to evaluate in the WHERE or HAVING clause expression. You can use a subquery instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause.
You can use three forms of syntax to create a subquery:Ĭomparison ( sqlstatement)Īn expression and a comparison operator that compares the expression with the results of the subquery.Īn expression for which the result set of the subquery is searched.Ī SELECT statement, following the same format and rules as any other SELECT statement.
MICROSOFT ACCESS DATABASE EXAMPLES UPDATE
A subquery is a SELECT statement nested inside a SELECT, SELECT…INTO, INSERT…INTO, DELETE, or UPDATE statement or inside another subquery.