Sub Query



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:

SELECT FirstName, LastName
FROM Customers
WHERE CustomerID IN(SELECT CustomerID
                    FROM Orders
                    WHERE ItemID = 25);


Results:

FirstName LastName
John Black

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:

SELECT COUNT(CustomerID) AS UniqueCustomers
FROM (SELECT DISTINCT CustomerID
      FROM Orders);


Results:

UniqueCustomers
2

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.

HTML 5 Badge