Lesson 2	Special extensions for the IN clause
Objective	Two Proper Syntax Formats for the IN Clause using subquery

Identify Two Proper Syntax Formats for IN Clause

The most common use of a subquery is probably the dynamic list^[1].

You need a list of values to compare to the data you are querying.
You create a dynamic list using a subquery^[2] so that your query is more flexible.

For example, let us say you need a report that lists PRODUCTs purchased by customers listed by the state WHERE the customer lives. You could create a list using literal values like this:

('HI','WI','NE')

However, as soon as you get a customer in another state, you must change your list.
Using a subquery to build the list on the fly (dynamically) assures that the list of states is up-to-date. The query would look like this:

(SELECT DISTINCT STATE 
 FROM CUSTOMER)

Using One Column in the subquery

When you want to create a list of values using a query, use the IN clause and place a query inside the parentheses.
Question: What is the purpose of the Oracle IN operator and give an example.
The Oracle IN operator is used in SQL queries to match a value against a list of possible values. It allows the user to specify multiple values within a single SQL statement, simplifying the query and reducing the need for complex conditions.
The syntax for the IN operator is as follows:

SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2, ...);

For example, suppose you have a table called "employees" that contains information about the employees in a company. You want to retrieve the details of all employees who are either managers or sales representatives. You can use the IN operator to achieve this as follows:

SELECT *
FROM employees
WHERE job_title IN ('Manager', 'Sales Representative');

This query will return all rows from the "employees" table where the "job_title" column matches either "Manager" or "Sales Representative".
In summary, the IN operator in Oracle is used to match a value against a list of possible values, allowing the user to simplify SQL queries and reduce the need for complex conditions.

Using Multiple Columns in Subquery

The second form of the IN clause allows you to check two or more values together. This is useful when you have a primary key that is made up of more than one column, for example. The following series of images shows you the syntax of both kinds of subquery formats and examples of queries.

Select form Table

1) This shows the basic syntax for a query with a sub-query
Select col1, col2, ... FROM table WHERE col3 IN (SELECT col4 FROM table WHERE condition)

2) 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 LASTNAME, FIRSTNAME FROM CUSTOMER WHERE CUST_ID IN (SELECT CUST_ID FROM CUSTOMER_SALE WHERE TOTAL_SALE_AMOUNT > 40)

3) 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 col1, col2, ... FROM table WHERE(col3, col4)IN (SELECT col5, col6 FROM table WHERE condition)

4) What is the name of the product purchased by Amy Black — 4) Here is an example, using our Pet Store schema.

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)

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.

In the next lesson, you will learn the unique syntax that Oracle uses to define outer joins.

[1]Dynamic list: A list of values created by adding a subquery to a query. This kind of list is dynamic because the list of values always reflects the current values in the database.

[2]subquery: A query nested within another SQL statement. Unlike implicit queries, subqueries use a SELECT statement to retrieve data.