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

数据库join语句使用及效率说明在我们开发数据库的时候，一定要时刻考虑效率的要求个人建议当多表关联的时候，尽量少用inner join2010宋述臣第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(等值连接) 只返回两个表中联结字段相等的行举例如下：-表A记录如下：aID aNum1 a200501112 a200501123 a200501134 a200501145 a20050115表B记录如下:bID bName1 20060324012 20060324023 20060324034 20060324048 2006032408-1.left joinsql语句如下:select * from Aleft join Bon A.aID = B.bID结果如下:aID aNum bID bName1 a200501111 20060324012 a200501122 20060324023 a200501133 20060324034 a200501144 20060324045 a20050115NULL NULL（所影响的行数为 5 行）结果说明:left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).B表记录不足的地方均为NULL.-2.right joinsql语句如下:select * from Aright join Bon A.aID = B.bID结果如下:aID aNum bID bName1 a200501111 20060324012 a200501122 20060324023 a200501133 20060324034 a200501144 2006032404NULL NULL 8 2006032408（所影响的行数为 5 行）结果说明:仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.-3.inner joinsql语句如下:select * from Ainnerjoin Bon A.aID = B.bID结果如下:aID aNum bID bName1 a200501111 20060324012 a200501122 20060324023 a200501133 20060324034 a200501144 2006032404结果说明:很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.-注:LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2说明：table1, table2参数用于指定要将记录组合的表的名称。field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。compopr参数指定关系比较运算符：=， ， = 或 。如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误.时间漩涡 用上面提到的A、B表为例： select * from A left join ( select bID,bName from B where bID = 3 )T on T.bID = A.aID第2章 inner join，left join，right join图形解析现在有两张表: 1.部门表(branch)： 2.员工表(employee)： a.内连接(inner join)内连接就是获取 两张表共有的数据。Sql代码 1. SELECTb.branch,FROMbranchASbINNERJOINemployeeASeONb.id=e.branchidSELECT b.branch, FROM branch AS b INNER JOIN employee AS e ON b.id=e.branchid 结果： 如果Sql换个写法： Sql代码 1. SELECTb.branch,FROMemployeeASeINNERJOINbranchASbONb.id=e.branchidSELECT b.branch, FROM employee AS e INNER JOIN branch AS b ON b.id=e.branchid 结果： 呵呵，发现结果是一样的，不管内连接的哪张表，结果都是获取的两张表的共有数据！此外，还有一种写法，能达到同样的效果： Sql代码 1. SELECTb.branch,FROMemployeeASe,branchASbWHEREb.id=e.branchidSELECT b.branch, FROM employee AS e,branch AS b WHERE b.id=e.branchid 结果： b.左连接(left join):Sql代码 1. SELECTb.branch,FROMbranchASbLEFTJOINemployeeASeONb.id=e.branchidSELECT b.branch, FROM branch AS b LEFT JOIN employee AS e ON b.id=e.branchid 结果： 分析下，为什么会有这样的结果，找个诀窍，看Sql语句的 from 关键字的后面是哪张表(branch表)，那么结果一定包含branch表中的所查询的字段(branch字段)所关联左表(employee表)的所有数据，如果左连接表(employee表中)不存在branch中的所查询字段，那么该字段的值为Null。好了，现在换个写法： Sql代码 1. SELECTb.branch,FROMemployeeASeLEFTJOINbranchASbONb.id=e.branchidSELECT b.branch, FROM employee AS e LEFT JOIN branch AS b ON b.id=e.branchid 结果： 在分析下，看Sql语句的 from 关键字的后面是哪张表(employee 表)，那么结果一定包含employee 表中的所查询的字段(name字段)所关联左表(branch表)的所有数据。c.右连接(right join):第3章 INNER JOIN和left JOIN假如我们描述这样一种关系，表1存储的是区域和区域ID,表二代表的是微软公司的子公司以及子公司所在的区域 1、表1CREATE TABLE region ( regionid int IDENTITY (1, 1) NOT NULL , regionname varchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL , CONSTRAINT PK_region PRIMARY KEY CLUSTERED ( regionid ) ON PRIMARY ) ON PRIMARYGO2、假如我们描述这样一种关系CREATE TABLE MicroSoft ( subId int IDENTITY (1, 1) NOT NULL , regionid int NOT NULL , name varchar(50) null, CONSTRAINT PK_microsoft PRIMARY KEY CLUSTERED ( subId ) ON PRIMARY ) ON PRIMARYGO3.1 现在比较：left join 和 inner join SELECT * FROM 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 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积第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.emp_no,t.emp_name,t.dep_no from employee where t.emp_no 80707999 ) 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 中Inner join 和where的效率差异各大论坛，包括MSDN上很多人提出了这个问题，但回答是众说纷纭。总体上总结出来时说：对小数据量（N万）的来说效率几乎无差异，更有说法说Inner join 和Where只是SQL标准不同，在查询分析器中SQL Server查询分析器是将Where直接转换为Join后查询的。还是自己来做试验吧。如是有了如下比较结果（均在查询分析器中查询和计时）： 语句（1）declare operatorName nvarchar(50)set operatorName = %select distinct item.* from item , customer_item , customer_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_operator.deleted = 0查询结果，74行，共时间0：00：04语句（2）declare operatorName nvarchar(50)set 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.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中，语句（3）declare operatorName nvarchar(50)set 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.operatorIdwhere operator.operatorName like operatorNameand item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0and customer_operator.ope