A subquery is a query within a query, it can be used to answer multiple-part questions. For example, to find all products made by the same manufacturer as product #45364: first use a subquery to determine the manufacturer of product #45364, then write the parent SELECT statement to return all the matching products.
Syntax comparison [ANY | ALL | SOME] (sqlstatement) expression [NOT] IN (sqlstatement) [NOT] EXISTS (sqlstatement) Key comparison An expression and a comparison operator that compares the expression with the results of the subquery. expression An expression for which the result set of the subquery is searched. sqlstatement A SELECT statement, following the same format and rules as any other SELECT statement.
The ALL predicate will retrieve only those records in the main query that satisfy the comparison with ALL the records retrieved by the subquery.
The ANY or SOME predicate, which are synonymous, will retrieve records in the main query that satisfy the comparison with ANY records retrieved by the subquery.
The IN predicate will retrieve only those records in the main query for which some record in the subquery contains an equal value.
The EXISTS predicate is a true/false comparison - whether the subquery returns any records.
Subqueries require you to write SQL, they can't be written with drag and drop in the QBE grid.
SELECT UnitPrice FROM PRODUCTS
WHERE UnitPrice < ALL (SELECT TOP 5 UnitPrice FROM Products ORDER BY UnitPrice DESC)
ORDER BY UnitPrice DESC;
WHERE EmployeeID IN (SELECT EmployeeID FROM Orders WHERE CustomerID = 'ALFKI');
' Delete invoices that have been receipted
DELETE FROM tblInvoice
(SELECT InvoiceID FROM tblReceipts WHERE tblReceipts.InvoiceID = tblInvoice.InvoiceID);
“We are in the process of creating what deserves to be called the idiot culture. Not an idiot sub-culture, which every society has bubbling beneath the surface and which can provide harmless fun; but the culture itself. For the first time, the weird and the stupid and the coarse are becoming our cultural norm, even our cultural ideal” ~ Carl Bernstein
Avg() - Average
Delete - Delete records.
Insert - Add records to a table (append query).
Select - Retrieve data from one or more tables or queries.
Select Into - Make-table query.
Transform - Create a crosstab query.
Union - Combine the results of two SQL queries.
Update - Update existing field values in a table.
Allen Browne - SubQuery examples