Related subqueries and nested subqueries [SQL Server]

Sqlserver subqueries can be divided into two types: Related subqueries and nested subqueries. Premise: assume that the books table is as follows:

Category Number book name Publishing price
--------------------------------------------------------
2 C # advanced application shengtong publishing 23.00
2 JSP development and application machinery Publishing House 45.00
3 advanced mathematics: Jinan press 25.00
3. Crazy English Tsinghua University Press 32.00

 

The execution of nested subqueries does not depend on external queries. Execution Process:

(1) When executing a subquery, the results are not displayed, but passed to the external query, which is used as a condition for the external query.

(2) execute an external query and display the entire result.

Nested subqueries can be divided into subqueries that return a single value and subqueries that return a list. The following is an example:

1. Return single value:

-- Query the names, authors, publishers, and prices of all books whose prices are higher than the average price.

Use tempdb
Go

Select book name, author, publisher, price
From books
Where price>
(
Select AVG (price)
From books
)
Go

2. Return Value List

-- Query the reader information of all borrowed books

Select *
From Readers
Where reader ID in
(
Select reader ID
From [borrow history]
)
Go

The execution of related subqueries depends on external queries. In most cases, the where clause of the subquery references the external query table. Execution Process:

(1) Extract A tuples from the outer query and pass the values of the related columns of the tuples to the inner query.

(2) execute the inner layer query to obtain the subquery operation value.

(3) The external query obtains rows that meet the conditions based on the results or result set returned by the subquery.

(4) then, the outer query retrieves the next tuples and repeats steps 1-3 until all the outer tuples are processed.

The following is an example:

-- Query the books whose prices are greater than the average prices of these books in the booka table

Select book name, publisher, class number, price
From books as
Where price>
(
Select AVG (price)
From books as B
Where a. Class number = B. Class number
)
Go

Different from the subquery described earlier, the related subquery cannot be solved independently of the external query. This subquery requires a value of "class number. This value is a variable and changes with sqlsever retrieving different rows in the books table. The following describes the query execution process in detail:

First, replace the value "2" of "class number" of the first record in the books table into the subquery, And the subquery becomes:

Select AVG (price)
From books as B
Where B. Class number = 2

The result of the subquery is the average price of this type of books, so the external query becomes:

Select book name, publisher, class number, price
From books as
Where price> 34

If the where condition is true, the first result is included in the result set, and no is included. Run the same process for all rows in the books table, the final result set and the final returned result.