sql语句中join的用法和效率说明.doc

《sql语句中join的用法和效率说明.doc》由会员分享，可在线阅读，更多相关《sql语句中join的用法和效率说明.doc（12页珍藏版）》请在课桌文档上搜索。

1、数据库join语句使用与效率说明第1章 目录第1章Sql之left join、right join、inner join的区别3第2章INNER JOIN和left JOIN82.1现在比较：left join 和 inner join9第3章提升left join效率11第4章SQL Server 中Inner join 和where的效率差异11第1章 Sql之left join、right join、inner join的区别 left join 返回包括左表中的所有记录和右表中联结字段相等的记录right join 返回包括右表中的所有记录和左表中联结字段相等的记录inner join

2、 只返回两个表中联结字段相等的行举例如下：-表A记录如下：aIDaNum1a200501112a200501123a200501134a200501145a20050115表B记录如下:bIDbName22006032402320060324034200603240482006032408-1.left joinsql语句如下:select * from Aleft join Bon A.aID = B.bID结果如下:aIDaNumbIDbName2a20050112220060324023a20050113320060324034a20050114420060324045a20050115

3、NULLNULL所影响的行数为 5 行结果说明:left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.换句话说,左表的记录将会全部表示出来,而右表只会显示符合搜索条件的记录.B表记录不足的地方均为NULL.-2.right joinsql语句如下:select * from Aright join Bon A.aID = B.bID结果如下:aIDaNumbIDbName2a20050112220060324023a20050113320060324034a2005011442006032404NULLNULL82006032408所影响的

4、行数为 5 行结果说明:仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表为基础的,A表不足的地方用NULL填充.-3.inner joinsql语句如下:select * from Ainnerjoin Bon A.aID = B.bID结果如下:aIDaNumbIDbName2a20050112220060324023a20050113320060324034a2005011442006032404结果说明:很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.-注:LEFT JOIN操作用于在任何

5、的 FROM 子句中,组合来源表的记录.使用 LEFT JOIN 运算来创建一个左边外部联接.左边外部联接将包含了从第一个左边开始的两个表中的全部记录,即使在第二个右边表中并没有相符值的记录.语法：FROM table1 LEFT JOIN table2 ON table1.field1 popr table2.field2说明：table1, table2参数用于指定要将记录组合的表的名称.field1, field2参数指定被联接的字段的名称.且这些字段必须有相同的数据类型与包含相同类型的数据,但它们不需要有相同的名称.popr参数指定关系比较运算符：=, , = 或 .如果在INNER

6、JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段,将会发生错误.时间漩涡 用上面提到的A、B表为例： select * from A left join select bID,bName from B where bID T on T.bID = A.aID第2章 inner join,left join,right join图形解析现在有两张表: 1.部门表： 2.员工表：a.内连接内连接就是获取 两张表共有的数据.Sql代码 1. SELECTb.branch,e.nameFROMbranchASbINNERJOINemployeeASeONb.id=

7、e.branchidSELECT b.branch,e.name FROM branch AS b INNER JOIN employee AS e ON b.id=e.branchid 结果： 如果Sql换个写法：Sql代码 1. SELECTb.branch,e.nameFROMemployeeASeINNERJOINbranchASbONb.id=e.branchidSELECT b.branch,e.name FROM employee AS e INNER JOIN branch AS b ON b.id=e.branchid 结果： 呵呵,发现结果是一样的,不管内连接的哪张表,结果

8、都是获取的两张表的共有数据！此外,还有一种写法,能达到同样的效果：Sql代码 1. SELECTb.branch,e.nameFROMemployeeASe,branchASbWHEREb.id=e.branchidSELECT b.branch,e.name FROM employee AS e,branch AS b WHERE b.id=e.branchid 结果： b.左连接:Sql代码 1. SELECTb.branch,e.nameFROMbranchASbLEFTJOINemployeeASeONb.id=e.branchidSELECT b.branch,e.name FROM

9、 branch AS b LEFT JOIN employee AS e ON b.id=e.branchid 结果： 分析下,为什么会有这样的结果,找个诀窍,看Sql语句的 from 关键字的后面是哪张表,那么结果一定包含branch表中的所查询的字段所关联左表的所有数据,如果左连接表不存在branch中的所查询字段,那么该字段的值为Null.好了,现在换个写法：Sql代码 1. SELECTb.branch,e.nameFROMemployeeASeLEFTJOINbranchASbONb.id=e.branchidSELECT b.branch,e.name FROM employee

10、AS e LEFT JOIN branch AS b ON b.id=e.branchid 结果： 在分析下,看Sql语句的 from 关键字的后面是哪张表,那么结果一定包含employee 表中的所查询的字段所关联左表的所有数据.c.右连接:第3章 INNER JOIN和left JOIN假如我们描述这样一种关系,表1存储的是区域和区域ID,表二代表的是微软公司的子公司以与子公司所在的区域 1、表1CREATE TABLE region regionid int IDENTITY NOT NULL , regionname varchar COLLATE Chinese_PRC_CI_AS

11、NOT NULL , CONSTRAINT PK_region PRIMARY KEY CLUSTERED ON PRIMARY ON PRIMARYGO2、假如我们描述这样一种关系CREATE TABLE MicroSoft subId int IDENTITY NOT NULL , regionid int NOT NULL , name varchar null, CONSTRAINT PK_microsoft PRIMARY KEY CLUSTERED ON PRIMARY ON PRIMARYGO3.1 现在比较：left join 和 inner joinSELECT * FROM

12、 microsoft t1 INNER JOIN region t2 ON t1.regionid = t2.regionidSELECT * FROM microsoft t1 left JOIN region t2 ON t1.regionid = t2.regionid由此可以看出,inner join 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行. 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行.如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值.右向外联接是左向外联接的反向联接.将返回

13、右表的所有行.如果右表的某行在左表中没有匹配行,则将为左表返回空值.完整外部联接返回左表和右表中的所有行.当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值.如果表之间有匹配行,则整个结果集行包含基表的数据值.交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合.交叉联接也称作笛卡尔积第4章 提升left join效率一：from子句中过滤数据后left join 跟 先left join后过滤数据的执行效率比较； 分别举例如下： test1： select t1.emp_no,t1.emp_name,t2.dep_no,t2.dep_name from select t

14、.emp_no,t.emp_name,t.dep_no from employee where t.emp_no t1 left join department t2 on t1.dep_no = t2.dep_no test2： select t1.emp_no,t1.emp_name,t2.dep_no,t2.dep_name from employee t1 left join department t2 on t1.dep_no = t2.dep_no where t1.emp_no 80707999在大数据量的情况下 test1 比 test2 效率高第5章 SQL Server 中

15、Inner join 和where的效率差异各大论坛,包括MSDN上很多人提出了这个问题,但回答是众说纷纭.总体上总结出来时说：对小数据量N万的来说效率几乎无差异,更有说法说Inner join 和Where只是SQL标准不同,在查询分析器中SQL Server查询分析器是将Where直接转换为Join后查询的.还是自己来做试验吧.如是有了如下比较结果均在查询分析器中查询和计时：语句1declare operatorName nvarcharset operatorName = %select distinct item.* from item , customer_item , custom

16、er_operator ,operatorwhere item.itemcode = customer_item.itemCode and customer_item.customerCode = customer_operator.customerCodeand customer_operator.operatorId = customer_operator.operatorIdand operator.operatorName like operatorNameand item.deleted = 0 and customer_item.deleted = 0 and customer_o

17、perator.deleted = 0查询结果,74行,共时间0：00：04语句2declare operatorName nvarcharset operatorName = %select distinct item.* from item inner join customer_item on item.itemcode = customer_item.itemCode inner join customer_operator on customer_item.customerCode = customer_operator.customerCodeinner join operator

18、 on customer_operator.operatorId = operator.operatorIdwhere operator.operatorName like operatorNameand item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0共74行,时间0：00：01后检查发现语句1中有一个重复自查询条件 ：customer_operator.operatorId = customer_operator.operatorId将其叶加到语句2中,语句3declare op

19、eratorName nvarcharset operatorName = %select distinct item.* from item inner join customer_item on item.itemcode = customer_item.itemCode inner join customer_operator on customer_item.customerCode = customer_operator.customerCodeinner join operator on customer_operator.operatorId = operator.operato

20、rIdwhere operator.operatorName like operatorNameand item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0and customer_operator.operatorId = customer_operator.operatorId所用时间和结果都为74行,时间0：00：01.将语句1中的去掉该条件后成为语句4declare operatorName nvarcharset operatorName = %select distinct

21、item.* from item , customer_item , customer_operator ,operatorwhere item.itemcode = customer_item.itemCode and customer_item.customerCode = customer_operator.customerCode-and customer_operator.operatorId = customer_operator.operatorIdand operator.operatorName like operatorNameand item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0时间和结果为74行,时间0：00：01.终于发现了些他们的差异.结论：尽量使用Join 而不是Where来列出关联条件,特别是多个表联合的时候.原因是：1在效率上,Where可能具有和Inner join一样的效率.但基本可以肯定的通过SQLServer帮助和其它资料,以与本测试是Join的效率不比Where差.2使用Join可以帮助检查语句中的无效或者误写的关联条件