Nest a query inside another query or in an expression by using a subquery
Sometimes you may want to use the results of a query as a field in another query, or as a criterion for a query field. For example, suppose that you want to see the interval between orders for each of your products. To create a query that shows this interval, you need to compare each order date to other order dates for that product. Comparing these order dates also requires a query. You can nest this query inside of your main query by using a subquery.
You can write a subquery in an expression or in a Structured Query Language (SQL) statement in SQL view.
In this article
Use the results of a query as a field in another query
You can use a subquery as a field alias. Use a subquery as a field alias when you want to use the subquery results as a field in your main query.
Note: A subquery that you use as a field alias cannot return more than one field.
You can use a subquery field alias to display values that depend on other values in the current row, which is not possible without using a subquery.
For example, let us return to the example where you want to see the interval between orders for each of your products. To determine this interval, you need to compare each order date to other order dates for that product. You can create a query that shows this information by using the Northwind database template.
-
On the File tab, click New.
-
Under Available Templates, click Sample Templates.
-
Click Northwind, and then click Create.
-
Follow the directions on the Northwind Traders page (on the Startup Screen object tab) to open the database, and then close the Login Dialog window.
-
On the Create tab, in the Queries group, click Query Design.
-
In the Show Table dialog box, click the Queries tab, and then double-click Product Orders.
-
Close the Show Table dialog box.
-
Double-click the Product ID field and the Order Date field to add them to the query design grid.
-
In the Sort row of the Product ID column of the grid, select Ascending.
-
In the Sort row of the Order Date column of the grid, select Descending.
-
In the third column of the grid, right-click the Field row, and then click Zoom on the shortcut menu.
-
In the Zoom dialog box, type or paste the following expression:
Prior Date: (SELECT MAX([Order Date])
FROM [Product Orders] AS [Old Orders]
WHERE [Old Orders].[Order Date] < [Product Orders].[Order Date]
AND [Old Orders].[Product ID] = [Product Orders].[Product ID])This expression is the subquery. For each row, the subquery selects the most recent order date that is less recent than the order date that is already associated with the row. Note how you use the AS keyword to create a table alias, so that you can compare values in the subquery to values in the current row of the main query.
-
In the fourth column of the grid, in the Field row, type the following expression:
Interval: [Order Date]-[Prior Date]
This expression calculates the interval between each order date and the prior order date for that product, using the value for prior date that we defined by using a subquery.
-
On the Design tab, in the Results group, click Run.
-
The query runs and displays a list of product names, order dates, prior order dates, and the interval between order dates. The results are sorted first by Product ID (in ascending order), and then by Order Date (in descending order).
-
Note: Because Product ID is a lookup field, by default, Access displays the lookup values (in this case, the product name), rather than the actual Product IDs. Although this changes the values that appear, it does not change the sort order.
-
-
Close the Northwind database.
Use a subquery as a criterion for a query field
You can use a subquery as a field criterion. Use a subquery as a field criterion when you want to use the results of the subquery to limit the values that the field displays.
For example, suppose that you want to review a list of orders that were processed by employees who are not sales representatives. To generate this list, you need to compare the employee ID for each order with a list of the employee IDs for employees who are not sales representatives. To create this list and to use it as a field criterion, you use a subquery, as shown in the following procedure:
-
Open Northwind.accdb and enable its content.
-
Close the login form.
-
On the Create tab, in the Other group, click Query Design.
-
In the Show Table dialog box, on the Tables tab, double-click Orders and Employees.
-
Close the Show Table dialog box.
-
In the Orders table, double-click the Employee ID field, the Order ID field, and the Order Date field to add them to the query design grid. In the Employees table, double-click the Job Title field to add it to the design grid.
-
Right-click the Criteria row of the Employee ID column, and then click Zoom on the shortcut menu.
-
In the Zoom box, type or paste the following expression:
IN (SELECT [ID] FROM [Employees]
WHERE [Job Title]<>'Sales Representative')This is the subquery. It selects all of the employee IDs where the employee does not have a job title of Sales Representative, and supplies that result set to the main query. The main query then checks to see whether employee IDs from the Orders table are in the result set.
-
On the Design tab, in the Results group, click Run.
The query runs, and the query results show a list of orders that were processed by employees who are not sales representatives.
Common SQL keywords that you can use with a subquery
There are several SQL keywords that you can use with a subquery:
Note: This list is not exhaustive. You can use any valid SQL keyword in a subquery, excluding data-definition keywords.
-
ALL Use ALL in a WHERE clause to retrieve rows that satisfy the condition when compared to every row returned by the subquery.
For example, suppose that you are analyzing student data at a college. The students must maintain a minimum GPA, which varies from major to major. Majors and their minimum GPAs are stored in a table named Majors, and the relevant student information is stored in a table called Student_Records.
To see a list of majors (and their minimum GPAs) for which every student with that major exceeds the minimum GPA, you can use the following query:
SELECT [Major], [Min_GPA]
FROM [Majors]
WHERE [Min_GPA] < ALL
(SELECT [GPA] FROM [Student_Records]
WHERE [Student_Records].[Major]=[Majors].[Major]); -
ANY Use ANY in a WHERE clause to retrieve rows that satisfy the condition when compared to at least one of the rows returned by the subquery.
For example, suppose that you are analyzing student data at a college. The students must maintain a minimum GPA, which varies from major to major. Majors and their minimum GPAs are stored in a table named Majors, and the relevant student information is stored in a table called Student_Records.
To see a list of majors (and their minimum GPAs) for which any student with that major does not meet the minimum GPA, you may use the following query:
SELECT [Major], [Min_GPA]
FROM [Majors]
WHERE [Min_GPA] > ANY
(SELECT [GPA] FROM [Student_Records]
WHERE [Student_Records].[Major]=[Majors].[Major]);Note: You can also use the SOME keyword for the same purpose; the SOME keyword is synonymous with ANY.
-
EXISTS Use EXISTS in a WHERE clause to indicate that a subquery should return at least one row. You can also preface EXISTS with NOT, to indicate that a subquery should not return any rows.
For example, the following query returns a list of products that are found in at least one existing order:
SELECT *
FROM [Products]
WHERE EXISTS
(SELECT * FROM [Order Details]
WHERE [Order Details].[Product ID]=[Products].[ID]);Using NOT EXISTS, the query returns a list of products that are not found in at least one existing order:
SELECT *
FROM [Products]
WHERE NOT EXISTS
(SELECT * FROM [Order Details]
WHERE [Order Details].[Product ID]=[Products].[ID]); -
IN Use IN in a WHERE clause to verify that a value in the current row of the main query is part of the set that the subquery returns. You can also preface IN with NOT, to verify that a value in the current row of the main query is not part of the set that the subquery returns.
For example, the following query returns a list of orders (with order dates) that were processed by employees who are not sales representatives:
SELECT [Order ID], [Order Date]
FROM [Orders]
WHERE [Employee ID] IN
(SELECT [ID] FROM [Employees]
WHERE [Job Title]<>'Sales Representative');By using NOT IN, you could write the same query this way:
SELECT [Order ID], [Order Date]
FROM [Orders]
WHERE [Employee ID] NOT IN
(SELECT [ID] FROM [Employees]
WHERE [Job Title]='Sales Representative');
No comments:
Post a Comment