Select - Subquery

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.

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 © 1999-2024 SS64.com
Some rights reserved