Select - Subquery

Retrieve data from one or more tables or queries.

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.

Examples

SELECT UnitPrice FROM PRODUCTS
WHERE UnitPrice < ALL (SELECT TOP 5 UnitPrice FROM Products ORDER BY UnitPrice DESC)
ORDER BY UnitPrice DESC;

SELECT *
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM Orders WHERE CustomerID = 'ALFKI');

' Delete invoices that have been receipted
DELETE FROM tblInvoice
WHERE EXISTS
(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

Related:

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


© Copyright SS64.com 1999-2013
Some rights reserved