Sql Server表连接查询的方法和性能探讨

    了解表连接的人，大概都觉得它很简单。其实简单只是它的外表，如果没有深刻理解Join语句，稍不留神就会有逻辑错误，逻辑错误比语法错误更难发现。

    要想正确使用Join语句，有2个知识点是必须掌握的。

第一：SQL语句执行的顺序

[c-sharp]

1. FROM  
2. JOIN ON  
3. WHERE  
4. GROUP BY  
5. HAVING  
6. SELECT  
7. ORDER BY  
8. FETCH FIRST   

第二：ON 和 WHERE 的区别：ON是用来定义连接条件的，WHERE用来过滤结果集

1、FROM ON 连接查询。我们来看一个例子，请看如下表定义：

[c-sharp]

1. CREATE TABLE EMPLOY  
2. (  
3.     NAME    VARCHAR(10),--姓名  
4.     DEPTNO    INTEGER--部门编号  
5. );  
6.    
7. INSERT INTO EMPLOY (NAME, DEPTNO) VALUES   
8. ('张三',10),  
9. ('李四',20),  
10. ('王五',10),  
11. ('赵红',20);  
12.    
13. CREATE TABLE DEPARTMENT  
14. (  
15.     DEPTNO    INTEGER,--部门编号  
16.     DEPTNAME    VARCHAR(10)--部门名  
17. );  
18.    
19. INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME) VALUES   
20. (10, '市场部'),  
21. (20, '技术部');  

我们看一条查询语句：

[c-sharp]

1. SELECT * FROM EMPLOY  E LEFT JOIN DEPARTMENT D   
2. ON E.DEPTNO=D.DEPTNO AND D.DEPTNO=40  

如果你认为上面这条SQL语句不会返回任何结果的话，说明你还不清楚ON 和 WHERE的区别。再次声明：ON是用来定义连接条件的，而不会过滤结果集。

我们再看一个例子，假设现在让你查询一下所有的员工的姓名和部门名为市场部的部门，怎么查？如下：

[c-sharp]

1. SELECT E.NAME,D.DEPTNAME FROM EMPLOY  E LEFT JOIN DEPARTMENT D ON E.DEPTNO=D.DEPTNO   
2. WHERE D.DEPTNAME='市场部'  

如果你也写出上面的语句，那么你就错了，正确的写法应该是这样：

[c-sharp]

1. 方法1：  
2. SELECT E.NAME,D.DEPTNAME FROM EMPLOY  E LEFT JOIN DEPARTMENT D ON E.DEPTNO=D.DEPTNO AND D.DEPTNAME='市场部'  
3.    
4. 方法2：  
5. SELECT E.NAME,D.DEPTNAME FROM EMPLOY  E LEFT JOIN   
6. (  
7.     SELECT * FROM DEPARTMENT WHERE DEPTNAME='市场部'  
8. ) AS D  
9. ON E.DEPTNO=D.DEPTNO   

在from join on and 时，应尽量在From 的and中加入筛选条件，看下面一个例子：

[c-sharp]

1. SELECT * FROM A INNER JOIN B ON A.ID=B.ID AND B.ID<>0
2. INNER JOIN C ON A.ID=C.ID AND C.ID<>0
4. SELECT * FROM A INNER JOIN B ON A.ID=B.ID
5. INNER JOIN C ON A.ID=C.ID 
6. WHERE B.ID<>0 AND C.ID<>0
8. --此写法效率比上面写法差,尤其表越多,效果越明显

思考From join on and 连接的的SQL执行顺序。前者两张表JOIN后，马上筛选部分结果在与另一张表JOIN，后者先将三张表JOIN后再筛选，所以很明显前者效率比后者高。
以上只是举了一个简单的例子，其实，使用JOIN的时候，还有好多这样的陷阱，如：在同一个语句中使用Inner Join和Outer Join的时候，应该特别注意。再如：多个表连接的时候，特别要注意连接条件，如：假设A、B、C三个表都有NO字段，以下2个语句可能产生截然不同的结果。

[c-sharp]

1. 语句1：  
2. A LEFT JOIN B ON A.NO=B.NO  
3. LEFT JOIN C ON A.NO=C.NO  
4.    
5. 语句2：  
6. A LEFT JOIN B ON A.NO=B.NO  
7. LEFT JOIN C ON B.NO=C.NO   

2、WHERE 条件连接。先看一个例子：

1. SELECT * FROM A, B, C
2. WHERE A.ID=B.ID AND A.ID=C.ID AND B.ID<>0 AND C.ID<>0

从逻辑上看，似乎ＳＱＬ　会先将表ＪＯＩＮ　后再筛选，但实战结果。是先筛选再ＪＯＩＮ　！因为ＳＱＬ　ＳＥＲＶＥＲ　会内部分析，产生一个最优的执行计划，所以不用你操心，自动帮你处理了！而使用ＪＯＩＮ　ＯＮ　的话，就好像是使用强制命令，告诉数据库，就是要按你的方式处理结果，数据库只好服从！！　所以思考ＳＱＬ写法不能只说要效率，同时还要注重结果对了，这才是关键。

两种连接查询各有优缺点，where中sql server会对其进行优化执行顺序，from中可以使用临时表（帅选后的数据）作为连接参照表，这样可以先筛选再连接，减少大数据量的连接。以便可以调高效率。同时结合索引的使用。使用where时，也有缺点，它很容易产生笛卡儿乘积，从而使结果集倍增，既使你指定了正确的连接条件，如果连接条件不唯一，也会产生局部笛卡儿乘积，如果这时使用分组统计，很可能产生不正确的结果，所以，也必须加倍小心。另外，使用from join on可以使表的关系比较清晰，看个人的喜欢吧。