What is the difference between the execution of SQL nested subqueries and correlated subqueries (recommended)

SQL Server subquery can be divided into two types: correlated subquery and nested sub-query. Premise

Suppose the books table is as follows:

Class number  book name         publishing house               price--------------------------------------------------------2      C # advanced App    San Tong Publishing            23.002      JSP Development and application   Machinery Publishing house          45.003      Advanced Mathematics       Jinan Press          25.003      crazy english       Tsinghua University Press     32.00

Execution of nested subqueries is not dependent on external queries.

Execution process:

(1) Executes the subquery, the result is not displayed, but is passed to the external query, as the condition of the external query to use.

(2) Executes an external query and displays the entire result.

Nested subqueries can generally be categorized as: A subquery that returns a single value, and a subquery that returns a list.

The following is an example of: 1. Return single value:  ------check all prices above the average price of the book name, author, publisher and Price. Use tempdb  GO  SElECT book name, author, publishing house, price from  Books  WHERE price >  (    SElECT AVG (price) from    Books  )  GO2. List of return values--query reader information for all borrowed books SElECT * from  Readers  WHERE reader number in  (    Select reader number from    [borrow History]  )  GO

The execution of the correlated subquery depends on the external query. In most cases, a table that references an external query in the WHERE clause of a subquery.

Execution process:

(1) Remove a tuple from the outer query and pass the value of the tuple-related column to the inner query.

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

(3) An outer query obtains rows that satisfy a condition based on the result or result set returned by the subquery.

(4) Then the outer query takes out the next tuple and repeats step 1-3 until the outer tuple is all processed.

The following examples illustrate:

Query the book information in the Booka table that is larger than the price average of the book Select Book name, publishing house, class number, price

  Select from Books as a  where price >  (    SELECT AVG (price)    from Books as B    WHERE A. Class number =b. Class number  )  go with The subquery described earlier is different, and the related subquery cannot be resolved independently of the external query. The subquery requires a value of "class number". This value is a variable that changes with Sqlsever to retrieve the different rows in the books table. The following details the query execution process: The  first record in the Books table in the "class number" of the value "2" into a subquery, the subquery becomes: SELECT AVG (price) from   Books as B    wher E B. Class Number = 2 The result of the subquery is the average price of the class book, so the external query becomes: SElECT book name, publishing house, class number, price from  Books as a  WHERE price > 34 IF If the Where condition is true, the first result is included in the result set, and no is included. Runs the same procedure for all rows in the books table, the resulting set of results, and the final return result.

What is the difference between the execution of SQL nested subqueries and correlated subqueries (recommended)