SQL Subqueries

Description

A subquery is a SELECT statement nested inside a SELECT, SELECT...INTO, INSERT...INTO, DELETE, or UPDATE statement or inside another subquery.

Syntax

You can use three forms of syntax to create a subquery:

comparison [ANY | ALL | SOME] (sqlstatement)

expression [NOT] IN (sqlstatement)

[NOT] EXISTS (sqlstatement)

A subquery has these parts:

Part	Description

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. It must be enclosed in parentheses.

Remarks

You can use a subquery instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause. 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.

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. 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
WHERE UnitPrice > ANY
(SELECT UnitPrice FROM OrderDetails
WHERE Discount >= .25);

Use the ALL predicate to retrieve only those records in the main query that satisfy the comparison with all records retrieved in the subquery. 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. This is much more restrictive.

Use the IN predicate to retrieve only those records in the main query for which some record in the subquery contains an equal value. The following example returns all products with a discount of 25 percent or more:

SELECT * FROM Products
WHERE ProductID IN
(SELECT ProductID FROM OrderDetails
WHERE Discount >= .25);

Conversely, you can use NOT IN to retrieve only those records in the main query for which no record in the subquery contains an equal value.

Use the EXISTS predicate (with the optional NOT reserved word) in true/false comparisons to determine whether the subquery returns any records.

You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees having the same job title. The Employees table is given the alias "T1":

SELECT LastName,
FirstName, Title, Salary
FROM Employees AS T1
WHERE Salary >=
(SELECT Avg(Salary)
FROM Employees
WHERE T1.Title = Employees.Title) Order by Title;

In the preceding example, the AS reserved word is optional.

Some subqueries are allowed in crosstab queries — specifically, as predicates (those in the WHERE clause). Subqueries as output (those in the SELECT list) are not allowed in crosstab queries.

See Also

ALL, DISTINCT, DISTINCTROW, TOP predicates, DELETE statement, HAVING clause, INNER JOIN operation, INSERT INTO statement, LEFT JOIN, RIGHT JOIN operations, SELECT statement, SELECT...INTO statement, UNION operation, UPDATE statement, WHERE clause.

Example

This example lists the name and contact of every customer who placed an order in the second quarter of 1995.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub SubQueryX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' List the name and contact of every customer 
    ' who placed an order in the second quarter of
    ' 1995.
    Set rst = dbs.OpenRecordset("SELECT ContactName," _
        & " CompanyName, ContactTitle, Phone" _
        & " FROM Customers" _
        & " WHERE CustomerID" _
        & " IN (SELECT CustomerID FROM Orders" _
        & " WHERE OrderDate Between #04/1/95#" _
        & " And #07/1/95#);")

    ' Populate the Recordset.
    rst.MoveLast

    ' Call EnumFields to print the contents of the 
    ' Recordset. Pass the Recordset object and desired
    ' field width.
    EnumFields rst, 25

    dbs.Close

End Sub

Example (Microsoft Access)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

The following example shows all non-discounted orders whose total is higher than the average order value:

SELECT OrderID, (UnitPrice * Quantity) As OrderTotal FROM [Order Details]
WHERE Discount = 0
AND (UnitPrice * Quantity) > ALL(SELECT Avg(UnitPrice * Quantity)
FROM [Order Details]);

The next example lists the name and unit price of every product whose unit price is the same as that of Aniseed Syrup:

SELECT ProductName, UnitPrice FROM Products
WHERE UnitPrice = (SELECT UnitPrice FROM [Products]
WHERE ProductName = 'Aniseed Syrup');

The following example lists the company and contact of every customer who placed an order in the second quarter of 1995:

SELECT ContactName, CompanyName, ContactTitle, Phone FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate
BETWEEN #04/1/95# AND #06/30/95#);

The next example selects the name of every employee who has booked at least one order. This could also be done with an INNER JOIN.

SELECT FirstName, LastName FROM Employees
WHERE EXISTS (SELECT OrderID FROM Orders
WHERE Orders.EmployeeID = Employees.EmployeeID);