Subquery format examples using Oracle

Select form Table

Select col1, col2, ...
FROM table
WHERE col3 IN (SELECT col4
               FROM table
               WHERE condition)

This shows the basic syntax for a query with a sub-query

SELECT LASTNAME, FIRSTNAME
FROM CUSTOMER 
WHERE CUST_ID IN
      (SELECT CUST_ID
       FROM CUSTOMER_SALE
       WHERE TOTAL_SALE_AMOUNT > 40)

Example of the query using the Pet Store Schema's CUSTOMER table and CUSTOMER_SALE table.
This query answers the question: Which customers made purchases greater than $40?

SELECT col1, col2, ...
FROM table 
WHERE(col3, col4)IN (SELECT col5, col6
  FROM table 
  WHERE condition)

This is the basic syntax of the second format for writing a query with a subquery. The main difference is that a list of columns is placed inside a set of parenthesis in the WHERE clause. The subquery also contains a list of columns in the SELECT clause rather than one column. Notice that the columns in the subquery SELECT clause are not placed in their own parenthesis.

SELECT PRODUCT_NAME
FROM PRODUCT P, CUSTOMER C
WHERE
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.