MS-Access / Getting Started

---

Subquery

Selects from a single column any number of values, or no values at all, for comparison in a predicate. You can also use a subquery that returns a single value in the select list of a SELECT clause.

Syntax

(SELECT [ALL | DISTINCT | DISTINCTROW | TOP number
	[PERCENT]] <select-list>
  FROM {table-name [[AS] correlation-name] |
    select-query-name [[AS] correlation-name] |
    <joined table>},...
    [WHERE <search-condition>]
    [GROUP BY column-name,...]
    [HAVING <search-condition>]
    [ORDER BY {column-name [ASC | DESC]},...])

where select-list is

{* | {<expression> | table-name.* |
  query-name.* | correlation-name.*}}

and where <joined table> is

({table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  (<select-statement>) AS correlation-name |
  <joined table>}
{INNER | {{LEFT | RIGHT | FULL} [OUTER]} JOIN
  {table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  (<select-statement>) AS correlation-name |
  <joined table>}
ON <join-specification>)

Notes: You can use the special asterisk (*) character in the <select-list> of a subquery only when the subquery is used in an EXISTS predicate or when the FROM clause within the subquery refers to a single table or query that contains only one column.

You can supply a correlation name for each table name or query name and use this correlation name as an alias for the full table name when qualifying column names in the <selectlist>, in the <join-specification>, or in the WHERE clause and subclauses. If you're joining a table or a query to itself, you must use correlation names to clarify which copy of the table or query you're referring to in the select list, join criteria, or selection criteria. You must also use a correlation name if one of the tables in the FROM clause is the same as a table in the outer query. If a table name or a query name is also an SQL reserved word (for example, Order), you must enclose the name in brackets. In SQL Server, you must enclose the name of a table or query that is also an SQL reserved word in double quotes. Note that when you open a query in an Access project, Access includes the required SET QUOTED_IDENTIFIER ON command in the command string. However, if you execute an SQL Server query from a desktop database with a pass-through query, you must include this command in the passthrough query.

When you list more than one table or query without join criteria, the source is the Cartesian product of all the tables. For example, FROM TableA, TableB instructs the database to search all the rows of TableA matched with all the rows of TableB. Unless you specify other restricting criteria, the number of logical rows that the database processes could equal the number of rows in TableA times the number of rows in TableB. The database then returns the rows in which the selection criteria specified in the WHERE and HAVING clauses are true.

You can further define which rows the database includes in the output recordset by specifying ALL, DISTINCT, DISTINCTROW (in a desktop database only), TOP n, or TOP n PERCENT. ALL includes all rows that match the search criteria from the source tables, including potential duplicate rows. DISTINCT requests that the database return only rows that are different from any other row.

DISTINCTROW (the default in Access version 7.0 and earlier) requests that Access return only rows in which the concatenation of the primary keys from all tables supplying output columns is unique. Depending on the columns you select, you might see rows in the result that contain duplicate values, but each row in the result is derived from a distinct combination of rows in the underlying tables. DISTINCTROW is significant only when you include a join in a query and do not include output columns from all tables.

Specify TOP n or TOP n PERCENT to request that the recordset contain only the first n or first n percent of rows. In general, you should specify an ORDER BY clause when you use TOP to indicate the sequence that defines which rows are first, or top. The parameter n must be an integer and must be less than or equal to 100 if you include the PERCENT keyword. If you do not include an ORDER BY clause, the sequence of rows returned is undefined. In a TOP query, if the nth and any rows immediately following the nth row are duplicates, the database returns the duplicates; thus, the recordset might have more than n rows. Note that if you specify an order, using TOP does not cause the query to execute any faster; the database must still solve the entire query, order the rows, and return the top rows.

In the search condition of the WHERE clause of a subquery, you can use an outer reference to refer to the columns of any table or query that is defined in the outer queries. You must qualify the column name if the table or query reference is ambiguous.

A column name in the GROUP BY clause can refer to any column from any table in the FROM clause, even if the column is not named in the <select-list>. If the GROUP BY clause is preceded by a WHERE clause, the database creates the groups from the rows selected after the application of the WHERE clause.

When you include a GROUP BY or HAVING clause in a SELECT statement, the select list must be made up of either SQL aggregate functions or column names specified in the GROUP BY clause. If a GROUP BY clause precedes a HAVING clause, the HAVING clause's search condition applies to each of the groups formed by equal values in the specified columns. If you do not include a GROUP BY clause, the HAVING clause's search condition applies to the entire logical table defined by the SELECT statement.

Examples

To find all contacts who own at least one product, enter the following (qxmplContactSomeProduct):

SELECT tblContacts.FirstName, tblContacts.MiddleInit, tblContacts.LastName
 FROM tblContacts
 WHERE EXISTS
  (SELECT *
   FROM tblContactProducts
   INNER JOIN tblProducts
   ON tblContactProducts.ProductID = tblProducts.ProductID
   WHERE tblContactProducts.ContactID = tblContacts.ContactID
   AND tblProducts.TrialVersion = 0);

Note: In this example, the inner subquery makes a reference to the tblContacts table in the SELECT statement by referring to a column in the outer table (tblContacts.ContactID). This forces the subquery to be evaluated for every row in the SELECT statement, which might not be the most efficient way to achieve the desired result. (This type of subquery is also called a correlated subquery.) Whenever possible, the database query plan optimizer solves the query efficiently by reconstructing the query internally as a join between the source specified in the FROM clause and the subquery. In many cases, you can perform this reconstruction yourself, but the purpose of the query might not be as clear as when you state the problem using a subquery.

To select contacts who first purchased a product before 2011 and list them in ascending order by postal code, enter the following (qxmplContactsPurchaseBefore2011):

SELECT TOP 100 PERCENT tblContacts.FirstName, tblContacts.MiddleInit,
  tblContacts.LastName, tblContacts.HomeCity, tblContacts.HomePostalCode
 FROM tblContacts
 WHERE #01/01/2011# >
  (SELECT Min(tblContactProducts.DateSold)
   FROM tblContactProducts
   WHERE tblContactProducts.ContactID = tblContacts.ContactID)
 ORDER BY tblContacts.HomePostalCode;

Note: The previous query also uses a correlated subquery.

To find the products whose price is greater than any of the support products, enter the following (qxmplProductsPrice>AnySupport):

SELECT tblProducts.ProductID, tblProducts.ProductName, tblProducts.UnitPrice
 FROM tblProducts
 WHERE tblProducts.UnitPrice >Any
  (SELECT tblProducts.UnitPrice
   FROM tblProducts
   WHERE tblProducts.CategoryDescription = "Support");

See also Expression, Predicates (BETWEEN, Comparison, EXISTS, IN, LIKE NULL, and Quantified), and SELECT Statement in this tutorial.