Many SQL Server programmers are confused about the use of subqueries, especially nested subqueries (that is, subqueries contain a subquery ). Now let's trace this problem.
There are two subquery types: standard and related. The standard subquery is executed once and the result is fed back to the parent query. Each row of the related subquery is executed and retrieved by the parent query. In this article, I will focus on nested subqueries (nested subqueries) (I will introduce related subqueries later ).
Think about this problem: You want to generate a list of sales personnel who sell flat washers. The data you need is scattered in four tables: personnel. contact information (Person. contact), HR. employee (HumanResources. employee), sales. sales Order title (Sales. salesOrderHeader), sales. sales order details (Sales. salesOrderDetail ). In SQL Server, you write programs from the internal pressure (outside-in), but it is very helpful to consider from the internal pressure (inside-out, that is, a required statement can be solved at one time.
If it is written from the inside out, you can check the Sales. SalesOrderDetail table and match the number of products (ProductNumber) value in the LIKE statement. You can connect these rows to the Sales. SalesOrderHeader table to obtain the Sales staff IDs (SalesPersonIDs ). Then, use SalesPersonID to connect to the SalesPersonID table. Finally, use ContactID to connect to the Person. Contact table.
Copy codeThe Code is as follows: 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 find that you can replace the SELECT statement with the IN () parameter. In this example, there are two applications, so a nested subquery is created.
I am a normalization enthusiast, though I don't accept its absurd length. Standardization increases complexity due to various queries. In these cases, subqueries are very useful, and nested subqueries are even more useful.
When the issues you need are dispersed in many tables, you must put them together again. In this case, you may find nested subprograms useful.
