Professional SQL Server™ 2005 Programming

6.2. Building a Nested Subquery

A nested subquery is one that goes in only one direction — returning either a single value for use in the outer query, orperhaps a list of values to be used with the IN operator. In the event you want to usean explicit "="operator, then you're going to be using a query that returns a singlevalue — that means one column from one row. If you are expecting a list back, then you'll need to use the IN operator with your outer query.

In the loosest sense, your query syntax is going to look something like one of these two syntax templates:

SELECT <SELECT list>
FROM <SomeTable>
WHERE <SomeColumn> = (
          SELECT <single column>
          FROM <SomeTable>
          WHERE <condition that results in only one row returned>)

Or:

SELECT <SELECT list>
FROM <SomeTable>
WHERE <SomeColumn> IN (
          SELECT <single column>
          FROM <SomeTable>
          [WHERE <condition>)]

Obviously, the exact syntax will vary. Not for just substituting the select list and exact table names, but also because you may have a multi-table join in either t e inner or outer queries — or both.

6.2.1. Nested Queries Using Single Value SELECT Statements

Let's get down to the nitty-gritty with an explicit example. Let's say, for example, that we wanted to know the ProductIDs of every item sold on the first day any product was purchased from thesystem.

If you already know the first day that an order was placed in the system, then it's no problem; the query would look something like this:

USE AdventureWorks SELECT DISTINCT soh.OrderDate, ...