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: