SUBQUERIES

SUBQUERIES

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: 

• WHERE column IN (subquery

• WHERE column <comparison> (subquery)

• WHERE EXISTS (subquery)

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

 Examples 

• IN                       

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");

                                    

This JOIN should give the same result:

            SELECT ProductName, UnitPrice

            FROM Products

                        INNER JOIN Categories

                        ON Products.CategoryID = Categories.CategoryID

            WHERE CategoryName = "Condiments";

• With comparison operator 

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;

• Exercises

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

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

• EXISTS

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*");

Result: