Subqueries in Access SQL: Queries Within Queries

A subquery is a SELECT statement nested inside another SQL statement. It lets you use the result of one query as a value, a list, or a filter condition in another query. Subqueries solve problems that are difficult or impossible to express with simple JOINs.

Types of Subqueries

Scalar Subquery

Returns a single value and can be used anywhere a value is expected:

SELECT ProductName, UnitPrice,
       (SELECT Avg(UnitPrice) FROM Products) AS AvgPrice,
       UnitPrice - (SELECT Avg(UnitPrice) FROM Products) AS DiffFromAvg
FROM Products;

This shows each product's price alongside the average price and the difference.

Subquery in WHERE with IN

Returns a list of values and filters the outer query to records matching that list:

-- Find customers who have placed at least one order
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);

-- Find customers who have NEVER placed an order
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders);

The NOT IN pattern is particularly useful — it is the cleanest way to find records in one table that have no matching records in another.

Subquery in WHERE with EXISTS

EXISTS returns True if the subquery returns any rows. It is often faster than IN for large datasets because it stops as soon as it finds the first match:

-- Find customers with at least one order over $1000
SELECT CustomerID, CustomerName
FROM Customers AS c
WHERE EXISTS (
    SELECT 1 FROM Orders AS o
    WHERE o.CustomerID = c.CustomerID
    AND o.OrderTotal > 1000
);

Note that the subquery references c.CustomerID from the outer query — this is a correlated subquery (see below).

Subquery in FROM

A subquery in the FROM clause creates a virtual table that the outer query can select from:

SELECT CustomerID, TotalOrders, TotalRevenue
FROM (
    SELECT CustomerID, 
           Count(OrderID) AS TotalOrders,
           Sum(OrderTotal) AS TotalRevenue
    FROM Orders
    GROUP BY CustomerID
) AS OrderSummary
WHERE TotalRevenue > 5000;

In Access, subqueries in the FROM clause must be given an alias (here: AS OrderSummary).

Correlated Subqueries

A correlated subquery references a column from the outer query. It is re-evaluated for every row in the outer query, which makes it powerful but potentially slow.

Example: Find the most recent order for each customer:

SELECT c.CustomerName, o.OrderDate, o.OrderTotal
FROM Customers AS c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate = (
    SELECT Max(OrderDate) 
    FROM Orders AS o2 
    WHERE o2.CustomerID = c.CustomerID
);

The subquery finds the maximum order date for each customer, and the outer query returns only the order that matches that date.

Finding Duplicates with Subqueries

-- Find all records where the email appears more than once
SELECT CustomerID, Email
FROM Customers
WHERE Email IN (
    SELECT Email
    FROM Customers
    GROUP BY Email
    HAVING Count(*) > 1
)
ORDER BY Email;

Top N Per Group

Finding the top N records within each group is a classic subquery problem:

-- Find the top 3 orders by amount for each customer
SELECT o.CustomerID, o.OrderID, o.OrderTotal
FROM Orders AS o
WHERE o.OrderID IN (
    SELECT TOP 3 OrderID
    FROM Orders AS o2
    WHERE o2.CustomerID = o.CustomerID
    ORDER BY OrderTotal DESC
)
ORDER BY o.CustomerID, o.OrderTotal DESC;

Subqueries vs. JOINs

Both subqueries and JOINs can solve many of the same problems. General guidelines:

Use a JOIN when:

• You need columns from both tables in the result
• Performance is critical (JOINs are usually faster)
• The relationship is straightforward

Use a subquery when:

• You need NOT IN / NOT EXISTS (finding non-matching records)
• You need a scalar value from another table
• The logic is clearer as a subquery
• You need to filter on an aggregate (HAVING in a subquery)

Performance Considerations

Correlated subqueries can be slow because they execute once per row in the outer query. For large tables, consider:

1. Rewrite as a JOIN — often faster for correlated subqueries
2. Create a saved query — Access can optimize a saved query better than an inline subquery
3. Use a make-table query — materialize the subquery result into a temp table

Subqueries in Access: Syntax Notes

Access SQL supports subqueries in WHERE, FROM, and SELECT clauses. A few Access-specific notes:

• Subqueries in FROM must have an alias: (SELECT ...) AS alias
• Access does not support WITH (Common Table Expressions) — use saved queries instead
• Very deeply nested subqueries can cause "Query is too complex" errors — break them into saved queries

Conclusion

Subqueries are one of the most powerful tools in SQL, enabling patterns — like finding non-matching records, top-N per group, and scalar lookups — that are difficult to express any other way. Master the IN, NOT IN, EXISTS, and scalar subquery patterns and you will be able to answer complex data questions that would otherwise require multiple queries and manual work.