Background:
You want to improve the efficiency of queries, starting with the frequently occurring subqueries in SQL statements.
The data table is as follows: The Citycode in the student table corresponds to code in the city table.
Student table: City table:
Sub-query mode:
The SQL statements are as follows:
1 Select from 2where 3 in 4 (select from City )
Inner Connection mode:
The SQL statements are as follows:
1 Select from 2 Student a INNER join City b 3 on A.citycode=b.code
The results of the search are consistent,
For simple retrieval, especially in the case of small amount of data, there is no difference between the two, it may arouse the doubts of the students, by what means that the way of connection than sub-query way better, can not get the evidence, I kill you.
As a result, you need to use SQL Server performance Tuning metrics, before SQL Server query statements, add the following two lines of statements, SQL statements are as follows:
1 Set Statistics time on 2 set statistics IO on
Then, open the message label to the right of the result label, which is the message inside the connection:
Here is a message for the subquery:
Note that the city table logically reads 8 times in the subquery, and only 1 times in the connection. Do not care about time, because the amount of data is small, so the sub-query faster, and so on the amount of data up, you understand.
Optimized subquery SQL statement for INNER JOIN