| Lesson 2 | Special extensions for the IN clause |
| Objective | Identify Two Proper Syntax Formats for the IN Clause. |
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.
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 SlideShow shows you the syntax of both kinds of subquery formats and examples of queries.
- This shows the basic syntax for a query with a sub-query
- Example of the query using the Pet Store Schema
- This is the basic syntax of the second format for writing a query
- What is the name of the product purchased by Amy Black
[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.