Working with nested subqueries in SQL Server
|
|
|
|
| Articles Reviews Structured Query Language | |
| Written by Adi Bach | |
| Saturday, 10 February 2007 | |
|
{mos_sb_discuss:29} Many SQL Server programmers are confused about the use of subqueries, especially nested subqueries (i.e., a subquery that contains a subquery). Let's start with the basics.
There are two kinds of subqueries: standard and correlated. The standard subquery executes once, and its results are fed into the parent query. A correlated subquery executes once for every row retrieved by the parent query. In this tip, I shed some light on the power of nested subqueries (I'll save correlated subqueries for another day). Consider this problem: You want to generate a list of the salespeople who have sold flat washers. The data you need is scattered among four tables: Person.Contact, HumanResources.Employee, Sales.SalesOrderHeader, and Sales.SalesOrderDetail. In SQL Server, you write it outside-in, but it's often helpful to think about it inside-out first, i.e., working out as far as required one statement at a time. Working from the inside out, you examine the Sales.SalesOrderDetail table, matching the ProductNumber value with a LIKE statement. You connect these rows to the Sales.SalesOrderHeader table from which you obtain the SalesPersonIDs. Then you connect to the HumanResources.Employee table using the SalesPersonID. Finally, you use the ContactID to connect to the Person.Contact table. User reviews
 There are no user reviews for this item. Add new review Powered by jReviews |
|
| Last Updated ( Saturday, 07 July 2007 ) | |
| < Prev | Next > |
|---|
