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

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

Note that in the outer query, we define an alias (or variable) P .Foreach
tuple in Products ,thevariable P in the inner query is instantiated with the
values in such tuple; if the result set of the inner query instantiated in this
way is not empty, the EXISTS predicate evaluates to true, and the values of
the attributes ProductID and ProductName are listed. The process is repeated
for all tuples in Products . Below, we show the result of the query, obviously
the same in both ways of writing it.

ProductID ProductName
1 Chai

2 Chang

3 Aniseed Syrup

... ...

To illustrate the NOT EXISTS predicate, consider the query “Names of
customers who have not purchased any product,” which is written as follows:

SELECT C.CompanyName
FROM Customers C
WHERE NOT EXISTS (
SELECT *
FROM Orders O
WHERE C.CustomerID = O.CustomerID )

Here, the NOT EXISTS predicate will evaluate to true if when P is instantiated
in the inner query, the query returns the empty set. The result is as follows.

CompanyName
FISSA Fabrica Inter. Salchichas S.A.
Paris specialites

Views

A view is just an SQL query that is stored in the database with an associated
name. Thus, views are like virtual tables. A view can be created from one or
many tables or other views, depending on the SQL query that defines it.