It is worth noting that most sub-queries can be written using joins. Where connections are available, connections are preferred for many reasons.

Nested sub-queries are nested only in one direction -- returned in. If you want to return a list, a single value used in an external query, or a complete list of values used with the IN operator. When you need to use a displayed "=" operator, you use a query that returns a single value -- that is, a column in a row. Then you need to use the IN operator in the external query.

In a stringent sense, the query grammar looks like the following two grammar 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 change, not only because of the need to replace the selection list and the exact table name, but also because there is a multi-table join in the internal query and/or the external query.

1.1.1 Nested queries using SELECT statements that return multiple values

Here's an explicit example to understand the nature of the problem. For example, suppose you want to know the Product ID of each item of a product sold through the system on the first day (note that OrderDate is of type Date)

SELECT DISTINCT soh.OrderDate, sod.ProductID
	FROM Sales.SalesOrderHeader sod
	JOIN Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
	WHERE OrderDate = (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader)	

Perhaps the most common sub-query is to retrieve some form of domain list and then use it as a condition for another query.

Here's an example of querying a list of all products with discount information.

SELECT ProductID, Name
	FROM Production.Product
	WHERE ProductID IN (
		SELECT ProductID FROM Sales.SpecialOfferProduct ):
	)	

Although this works well, it's better to use internal connections rather than nested SELECT. For example, the same results can be obtained by allowing simple connections.

SELECT DISTINCT pp.ProductID, Name
	FROM Production.Product PP
	JOIN Sales.SpecialOfferProduction ssop on pp.ProductID = ssop.ProductID;

Because of performance considerations, if there is no particular reason to use nested SELECT, the connection method should also be used as the default solution.

Tips:

SQL SERVER can handle this kind of problem flexibly. In most cases, SQL SERVER actually parses nested subquery methods into

Use the same query plan as the join -- in fact, if you examine the nested sub-queries and the previous join query plan, you will find that they are exactly the same.

Plan. Therefore, in most cases, there is little difference between the two methods. Of course, what I'm talking about here is "in most cases". When querying a plan

Connections are usually a better choice at different times, so it is recommended to use the syntax of connections by default.

This nested SELECT handles the addition of NOT operators almost the same as the previous examples. This has to be set to be equivalent to when transforming the join grammar

External connections rather than internal connections.

Let's take a look at the discount records for all unmatched items:

SELECT Description
	FROM Sales.SpecialOffer sso
	WHERE sso.SpecialOfferID != 1
		AND sso.SpecialOfferID NOT IN 
		(SELECT SpecialOfferID FROM Sales.SpecialOfferProduct);

The above functions can also be implemented by external connections.

		SELECT Description
		FROM Sales.SpecialOfferProduct ssop
		RIGHT OUTER JOIN Sales.SpecialOffer sso on sso.SpecialOfferID = ssop.SpecialOfferID
		WHERE sso.SpecialOfferID != 1 AND sso.SpecialOfferID IS NULL;

2.1 Principle of Associated Subquery

The difference between associative sub-queries and nested sub-queries above is that information transfer is bidirectional rather than one-way. In nested queries, internal queries are processed only once, and then information is passed to external queries.

External queries are executed only once -- providing exactly the same values or lists as you might enter yourself.

In related sub-queries, internal queries are allowed to use information provided by two external queries, and vice versa. This may seem confusing, but it's a three-step process.

(1) An external query obtains a record, which is then passed to an internal query.

(2) Internal queries are executed based on the passed values

(3) Then the internal query sends the result values back to the external query, and the external query uses these values to complete the processing process.

For the customer information of the first day order in the above query system, consider the date of the first day order for each customer in the query system. Here's what you need.

A little skill. When using nested sub-queries, only the first day is queried throughout the system -- now you need to find the first day order for each customer

Date. If you use two separate queries to complete, you can create a temporary table and then join it.

The solution using temporary tables is somewhat similar to the following:

USE AdventureWorks2008;

-- Get a list of customers and the date of their first order
SELECT soh.CustomerID, MIN(soh.OrderDate)AS OrderDate
INTO #MinOrderSates
>FROM Sales.SalesOrderHeader soh
GROUP BY soh.CustomerID

-- Do something additional with that information
SELECT soh.CustomerID, soh.SalesOrderID, soh.OrderDate
FROM Sales.SalesOrderHeader soh
JOIN #MinOrderDates t on soh.CustomerID = t.CustomerID  and soh.OrderDate = t.OrderDate 
ORDER BY soh.CustomerID  

DROP TABLE #MinOrderDates;

Sometimes, using multiple queries is the only way without cursors -- but this is not the case here.

If you want to be allowed in a query in China, you need to find a way to view each user. Internal queries can be searched based on Customer ID of the current external query. Then need

Pass the return value to the external query. This can be matched based on the earliest order date.

The code is as follows:

SELECT sob1.CustomerID, sob1.SalesOrderID, sob1.OrderDate
FROM Sales.SalesOrderDetail soh1
WHERE soh1.OrderDate = (
	SELECT Min(soh2.OrderDate) FROM Sales.SalesOrderDetail soh2
	WHERE soh2.CustomerID = soh1.CustomerID 
	)
ORDER BY CustomerID;

In this particular query, external queries refer only to internal queries in the WHERE clause -- or they can include data from internal queries in the selection list.

Subqueries can also be used to provide different answers in the selection results. This often happens when the information to be searched is completely different from other information to be queried for lunch (for example, if you need a field)

Aggregate the results, but don't want all this data to affect the other returned fields.

Here we check the customer's account and when to start ordering something. The code is as follows:

SELECT sc.AccountNumber,
	(SELECT Min(OrderDate) FROM Sales.SalesOrderHeader soh
	  WHERE sob.CustomerID = sc.CustomerID) AS OrderDate
FROM Sales.Customer sc;

3. MERGE command