RelationalDBDesign RelationalDBDesign


Join Queries  «Prev  Next»
Lesson 2 Special extensions for the IN clause
Objective Identify Two Proper Syntax Formats for the IN Clause.

Identify Two Proper Syntax Formats for the 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.

Using multiple columns in the 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 SlideShow shows you the syntax of both kinds of subquery formats and examples of queries.

  1. This shows the basic syntax for a query with a sub-query
  2. Example of the query using the Pet Store Schema
  3. This is the basic syntax of the second format for writing a query
  4. What is the name of the product purchased by Amy Black

Subquery Format Examples
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.