Until now, we've been getting data from the database using simple queries and a single SELECT statement. However, all the same, more often we will need to select data that meets many conditions, and here we cannot do without advanced queries. For this, in SQL there are subqueries or nested queries, where one SELECT statement is nested inside another.
A subquery is a SELECT statement nested inside another SELECT, SELECT...INTO, INSERT...INTO, DELETE, UPDATE, or inside another subquery.
The subquery text must be enclosed in parentheses. Subqueries are often used instead of joining tables (JOIN).
The database tables used in DBMS Access are relational tables, that is, all tables can be linked to each other by common fields. Suppose we store data in two different tables and we need to select data in one of them, depending on what data is in the other. For this, we will create another table in our database. This will be, for example, the table Sellers with information about suppliers:
Now we have two tables - Sumproduct and Sellers that have the same field City. Let's say we need to count how many products were sold in Canada alone. Subqueries will help us do this. So, first, let's write a query for a sample of cities located in Canada:
Run SQLSELECT City
FROM Sellers
WHERE Country = 'Canada'
Now let's pass this data to the following query, which will select data from the Sumproduct table:
Run SQLSELECT SUM(Quantity) AS Qty_Canada
FROM Sumproduct
WHERE City IN ('Montreal','Toronto')
We can also combine these two requests into one. Thus, one request that outputs data will be the main one, and the second request that passes the input data will be an auxiliary (sub-query). To insert a subquery, we will use the construction WHERE ... IN (...), which was discussed in the section Advanced filtering:
Run SQLSELECT SUM(Quantity) AS Qty_Canada
FROM Sumproduct
WHERE City IN (SELECT City
FROM Sellers
WHERE Country = 'Canada')
We can see that we got the same data as with two separate queries. In the same way, we can increase the nesting depth of queries by nesting subqueries as many times as we want.
We can also use subqueries as calculated fields. Let's display, for example, the number of sold products for each seller using the following query:
Run SQLSELECT Seller_name, (SELECT SUM(Quantity)
FROM Sumproduct
WHERE Sellers.City = Sumproduct.City) AS Qty
FROM Sellers
The first statement SELECT displays two columns - Seller_name and Qty. The field Qty is calculated, it is formed as a result of the execution of the subquery, which is enclosed in round brackets. This subquery is executed once for each record in the Seller_name field, and will be executed four times in total since four seller names are selected.
Also, in the subquery, the WHERE clause performs the join function, because with the help of WHERE we joined the two tables by the City field, using the full names of the columns (Table.Field).