Database Concepts - Data Warehouse Systems: Design and Implementation - page 38

product in an order, along with the quantity of the product in the order. We
can see that customer BLAUS has three orders of product 21. On the table
to the right, we see the final result: there is only one tuple for BLAUS and
product 21, with a total of 23 units ordered.

CustomerID ProductID Quantity
ALFKI

CustomerID ProductID TotalQty
ALFKI 58 40 58 40

ALFKI 39 21 ALFKI 39 21

... ... ... ... ... ...

BLAUS 21 12 BLAUS 21 23

BLAUS 21 8 ... ... ...

BLAUS 21 3

... ... ...

Subqueries

A subquery (or a nested query )isanSQLqueryusedwithina SELECT ,
FROM ,or WHERE clause. The external query is called the outer query .
In the WHERE clause, this is typically used to look for a certain value in
a database, and we use this value in a comparison condition through two
special predicates: IN and EXISTS (and their negated versions, NOT IN and
NOT EXISTS ).
As an example of the IN predicate, let us consider the query “Identifier
and name of products ordered by customers from Germany.” The query is
written as follows:

SELECT ProductID, ProductName
FROM Products P
WHERE P.ProductID IN (
SELECT D.ProductID
FROM Orders O JOIN Customers C ON O.CustomerID = C.CustomerID
JOIN OrderDetails D ON O.OrderID = D.OrderID
WHERE C.Country = ' Germany ' )

The inner query computes the products ordered by customers from Germany.
This returns a bag of product identifiers. The outer query scans the Products
table, and for each tuple, it compares the product identifier with the set
of identifiers returned by the inner query. If the product is in the set, the
product identifier and the product name are listed.
The query above can be formulated using the EXISTS predicate, yielding
what are denoted as correlated nested queries , as follows: