Subqueries are used to structure queries.  In  many cases, a subquery can be used instead of a JOIN (and vice versa).

For database systems fully compliant with the SQL 92 standard, a subquery can also be used to provide one or more values in the SELECT clause.

In most database systems, subqueries are typically part of the WHERE clause, as follows:


The subqueries themselves are complete SELECT statements, enclosed in parentheses.





Find the price of all products in a particular category, for example condiments.

Type this query in the SQL window and check against the result shown below:


            SELECT ProductName, UnitPrice

            FROM Products

            WHERE CategoryID In

                        (SELECT CategoryID

                        FROM Categories

                        WHERE CategoryName = "Condiments");

subquery using IN

This JOIN should give the same result:


            SELECT ProductName, UnitPrice

            FROM Products

                        INNER JOIN Categories

                        ON Products.CategoryID = Categories.CategoryID

            WHERE CategoryName = "Condiments";



Run the query Products Above Average Price; check in SQL view (shown here):

SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE (((Products.UnitPrice) >
   (SELECT AVG([UnitPrice]) From Products)))
ORDER BY Products.UnitPrice DESC;


Modify the query to show products below average price.  Results (formatting removed):

products below average price

Modify again to show products within plus or minus 10% of average price (requires some calculations)

products within 10% of average price


Create a list of suppliers from whom we buy dairy products.  Type the following query in the SQL window  - use the INNER JOIN ... ON syntax in the subquery, if you prefer.


            SELECT CompanyName

            FROM Suppliers AS s

            WHERE EXISTS

                        (SELECT *

                         FROM Products p, Categories c

                        WHERE p.SupplierID = s.SupplierID

                        AND p.CategoryID = c.CategoryID

                        AND CategoryName LIKE "*Dairy*");


dairy product suppliers - exists query