Related sub-query

SQL Server subquery to be divided into correlated subqueries and nested subqueries

Category number Book name publishing house price

2 C # Morimichi Publishing 23.00

2 JSP Development Machinery Publishing house 45.00

3 Advanced Mathematics Jinan Publishing house 25.00

3 Crazy English Tsinghua Press 32.00

1) Execution of nested subqueries is not dependent on external queries.

2) Execute the external query and display the results

--Query All prices above the average price of the book name, author, publishing house price

--Example 1

Use tempdb

Go

Select Title, author, publishing house, Price

From Books

where price

（

Select AVG (Price)

From Books

）

Go

The execution of a correlated subquery relies on an external query, which in many cases is a table in the WHERE clause of a subquery that references an external query

--Example 2

1) Remove a tuple from the outer query and pass the column values related to the tuple to the inner query.

2) Execute inner query to get the value of subquery.

Select Title, author, publishing house, Price

From book as a

where Price >

（

Select AVG (Price)

From Books as B

where A. Class number =b. Class number

）

Go

As you can see, the related query cannot be independent of the external query, which requires a numbered value and this value is a variable that changes with Sqlsever to retrieve different rows in the books table.

The "number" value "2" of the first record in the books table is first brought into the subquery subquery to:

Select AVG (Price)

From Books as B

where B. Class number =2

The result of the subquery is the average price for that class of books, so the external query becomes:

Select Title, author, publishing house, Price

From Books as B

where Price >34

If the Where condition is true, the first result is included in the result set, otherwise it is not included.

Summarize:

1) The execution of the correlated subquery relies on the data of the external query, the outer query executes one row, and the subquery executes once.

2) The non-correlated subquery is a subquery that is independent of the external query, and the subquery executes a total of one execution after the value is passed to the outside query.

Related sub-query