How to use SQL Server nested subqueries

Many SQL Server programmers are confused about the use of subqueries (subqueries), especially for nested subqueries (that is, a subquery that contains a subquery). Now, let's look at this question in the right way.

There are two types of seed queries: standard and related. The standard subquery executes once, and the results are fed back to the parent query. The related subquery is executed once per line and retrieved by the parent query. In this article, I'll focus on nested subqueries (nested subqueries) (I'll introduce related subqueries later).

Imagine the problem: you want to generate a list of salespeople who sell flat washers. The data you need is scattered across four tables: Personnel. Contact information (person.contact), human resources, staff (HumanResources.Employee), sales. Sales order title (Sales.SalesOrderHeader), sales, sales order Details (Sales.salesorderdeta IL). In SQL Server, you write the program from the internal pressure (outside-in), but it is very helpful to start with the outer pressure (inside-out), that is, you can resolve one of the required statements at a time.

If you write from inside to outside, you can check the Sales.SalesOrderDetail table and match the product number (ProductNumber) values in the like statement. You connect these lines to the Sales.SalesOrderHeader form, from which you can obtain the Salesperson IDs (Salespersonids). Then use SalesPersonID to connect the SalesPersonID table. Finally, use ContactID to connect the Person.Contact table.

The following are the referenced contents: Use AdventureWorks; Go SELECT DISTINCT C.lastname, C.firstname From Person.Contact c JOIN humanresources.employee E On e.contactid = C.contactid WHERE EmployeeID in (SELECT SalesPersonID From Sales.SalesOrderHeader WHERE SalesOrderID in (SELECT SalesOrderID From Sales.SalesOrderDetail WHERE ProductID in (SELECT ProductID From Production.Product P WHERE productnumber like ' fw% ')); Go

This example reveals several wonderful things about SQL Server. You can see that you can replace the SELECT statement with the in () parameter. In this case, there are two applications, so a nested subquery is created.

I am a normalization of standardization, though I do not accept its absurd length. Complexity is increased by the fact that standardization has a variety of queries. In these cases the query becomes very useful, and nested subqueries are even more useful.

When the problems you need are scattered across many tables, you have to put them together again, and you may find that nesting is useful.