Comparison of simple nested queries and non-nested queries in SQL

This article will discuss the similarities and differences between simple nested queries and non-nested queries in SQL, through which you can better understand SQL statements.

One day's work was to fix the bug of a project, and then we found that its SQL statements were extremely messy, there were a lot of left join and in operations, and nested queries had only one table nested query ). I don't know where I have seen it. The nested query is slow, so I changed all the nested queries to join. I wrote the where conditions in the nested queries to join... on. Suddenly an idea came out: What is the difference between filtering conditions following join... on and after where at the end of the entire SQL statement? Is nested query really slow? Therefore, the following test is generated, and the database environment is ms SQL 2005.

I. inner join

First look at non-nested queries:

 
 

  
  
a.select * from t1  
  
  
inner join t2 on t1.id = t2.id  
  
  
inner join t3 on t1.id = t3.id  
  
  
where t1.a=1 and t2.b=1 and t3.c=1  
  
  
 
  
  
b.select * from t1  
  
  
inner join t2 on t1.id = t2.id and t2.b=1   
  
  
inner join t3 on t1.id = t3.id  
  
  
where t1.a=1 and t3.c=1  
  
  
 
  
  
c.select * from t1  
  
  
inner join t2 on t1.id = t2.id and t2.b=1   
  
  
inner join t3 on t1.id = t3.id and t3.c=1  
  
  
where t1.a=1 
 
 

In the preceding three non-nested queries, let "and t2. B = 1" and "and t3.c = 1" join... on and where walk between, use Management Studio to select "contains the actual execution plan" and execute these three statements to get the following execution plan.

The predicates of the three clustered index scans are as follows:

1. t3.c = 1

2. t1.a = 1 (seek predicate: t1.id = t3.id)

3. t2. B = 1 (seek predicate: t2.id = t3.id)

Therefore, it can be considered that in MS SQL2005, conditions following join... on are equivalent to where.

SeeNested Query:

 
 

  
  
d.select * from t1  
  
  
inner join (select * from t2 where t2.b=1)a on t1.id=a.id  
  
  
inner join t3 on t1.id = t3.id  
  
  
where t1.a=1 and t3.c=1  
  
  
 
  
  
e.select * from t1  
  
  
inner join (select * from t2 where t2.b=1)a on t1.id=a.id  
  
  
inner join (select * from t3 where t3.c=1)b on t1.id=b.id  
  
  
where t1.a=1  
  
  
 
  
  
f.elect * from t1  
  
  
inner join (select t3.id,t2.b,t3.c from t3 inner join t2 on t2.id = t3.id where t2.b=1 and t3.c=1)a on t1.id=a.id  
  
  
where t1.a=1 
 
 

The first SQL statement converts the t2 Query into a subquery, the second SQL statement converts t2 and t3 into subqueries, and the third statement combines the t2 and t3 queries into a subquery, let's look at the actual execution plan:

The execution plan of the preceding non-nested query is the same.

Therefore, it can be considered that it is simple and complicated.) nested queries and their corresponding non-nested query forms should be considered, the execution efficiency is the same. Some online articles point out that the ms SQL optimizer has optimized these nested queries ).

Next, I found a small problem in the above two execution plans. Why is it clear that the select t1 inner join t2 inner join t3, the execution plan starts t1 and t3 inner join (t1.id = t3.id) and t2 inner joint2.id = t3.id?

After connecting three tables, four tables, and five tables, we found that the Order was uncertain. It is likely that these sequences are determined by the algorithms in the SQL optimizer. Because there is no source code, there is no way to study them.

(Thanks to Keep Walking for adding:

"Order, force order, and keep plan options can be specified.

Different orders of magnitude, indexes, and statistics can lead to sequential changes ". I googled option force order and option keep plan and found that the SQL optimizer has done a lot of things and won't list them in this article. If you are interested, you can go to Goo .)

PS:

1. After testing, t1.id = t2.id and t2.id = t1.id are equivalent

If you find any errors in this article, please note.

Original article title: SQL statement analysis: Comparison between ON and WHERE _ Comparison Between Simple nested query and non-nested Query

Link: http://www.cnblogs.com/StephenHuang/archive/2010/01/03/1637846.html

1. SQL Server executes proxy tasks through code
2. SQL Server is about to improve the real-time data Function
3. SQL Server question database solution