FROM PRODUCT P, CUSTOMER C
CUST_ID = 1
AND (PRODUCT_ID, CUST_ID) IN
(SELECT SI.PRODUCT_ID, CS.CUST_ID
FROM CUSTOMER_SALE CS, SALE_ITEM SI
WHERE CS.SALES_ID = SI.SALES_ID)
Here is an example, using our Pet Store schema. The query answers the question:
What is the name of the products that were purchased by Amy Black (CUST_ID=1)?
As shown here, you can use more than one criteria in your WHERE clause when you use the IN clause.
In addition, you see that the sub-query is joins two tables. You can see how this kind of query can get complex rather quickly. Even so, it is a useful tool to add to your collection.