The Language of SQL- P33:Research has shown that, being pressed for time, most readers tend to skip the introduction of any book they happen to read and then proceed immediately to the first real chapter. With that fact firmly in mind, we will only cover relatively unimportant material in the introduction, such as an explanation of what you will and will not learn by reading this book. | 146 Chapter 14 Subqueries In this format the IN operator merely lists a number of values in parentheses. There is also a second format for the IN one in which an entire SELECT statement is inserted inside the parentheses. For example a list of states might be specified as WHERE State IN SELECT States FROM StateTable WHERE Region Midwest Rather than list individual states the second format allows us to generate a list of states through more complex logic. Let s illustrate with an example that uses our Customers and Orders tables. Let s say we want to retrieve a list of customers who have ever paid cash for any order they ve placed. A SELECT that accomplishes this is SELECT CustomerName AS Customer Name FROM Customers WHERE CustomerID IN SELECT CustomerID FROM Orders WHERE OrderType Cash The resulting data is Customer Name William Smith Natalie Lopez Brenda Harper is not included in the list because she has never placed an order using cash. Notice that a subquery SELECT is placed entirely within the parentheses for the IN keyword. Also note that the CustomerID column is used to connect the two queries. Even though we are displaying CustomerName we use CustomerID to define the relationship between the Customers and Orders tables. Once again we can ask whether this subquery can also be expressed as a normal query and the answer is yes. Here is an equivalent query that returns the same data Correlated Subqueries 147 SELECT CustomerName AS Customer Name FROM Customers INNER JOIN Orders ON WHERE OrderType Cash GROUP BY Without using a subquery we can directly join the Customers and Orders table. However a GROUP BY clause is now needed to ensure that we only bring back one row per customer. Correlated Subqueries The subqueries we ve seen so far have been uncorrelated subqueries. Generally speaking all subqueries can be classified as either uncorrelated or correlated. These terms describe .
