SUBQUERIES

Used to structure queries. In many cases, can be used instead of JOIN (and vice versa).

In SQL 92 can also be used to provide one or more values in the SELECT clause.

Typically part of the WHERE clause, as follows:

WHERE column IN (subquery)

or

WHERE column <comparison> (subquery)

or

WHERE EXISTS (subquery)

Examples

IN

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

SELECT ProductName, UnitPrice

FROM Products

WHERE CategoryID In

(SELECT CategoryID

FROM Categories

WHERE CategoryName = "Condiments");

Same query using a JOIN:

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.

EXISTS

Create a list of suppliers from whom we buy dairy products:

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