Sub Queries are queries that are nested inside another query.
They can be used in the WHERE clause to retrict rows. They can also be used
in the FROM clause to Select a row that the outer or top query uses to base
its data selection off of.
Example sub query in a WHERE clause:
WHERE CustomerID IN(SELECT CustomerID
ItemID = 25);
The query in this case will return all customers who have ordered the item which
has an ItemID of 25. This type of sub query is for those case when you are not sure
what the value that is going to be returned by the nested query is.
Example of a sub query nested in the FROM clause:
COUNT(CustomerID) AS UniqueCustomers
FROM (SELECT DISTINCT CustomerID
The query in this case will return a count of all the unique CustomerID's. In this case
the top query is built off of the data selected in the nested query.
In many of the other DBMS SQL engines, you could use a SELECT DISTINCT COUNT in one query,
however, in the MS Access SQL engine a sub query must be used to achive the same result.