sql语句中join的用法和效率说明.docx
- 文档编号:4795835
- 上传时间:2022-12-09
- 格式:DOCX
- 页数:12
- 大小:69.01KB
sql语句中join的用法和效率说明.docx
《sql语句中join的用法和效率说明.docx》由会员分享,可在线阅读,更多相关《sql语句中join的用法和效率说明.docx(12页珍藏版)》请在冰豆网上搜索。
sql语句中join的用法和效率说明
数据库join语句使用与效率说明
在我们开发数据库的时候,一定要时刻考虑效率的要求
个人建议当多表关联的时候,尽量少用innerjoin
2010
宋述臣
第1章Sql之leftjoin、rightjoin、innerjoin的区别
leftjoin(左联接)返回包括左表中的所有记录和右表中联结字段相等的记录
rightjoin(右联接)返回包括右表中的所有记录和左表中联结字段相等的记录
innerjoin(等值连接)只返回两个表中联结字段相等的行
举例如下:
--------------------------------------------
表A记录如下:
aIDaNum
1a20050111
2a20050112
3a20050113
4a20050114
5a20050115
表B记录如下:
bIDbName
12006032401
22006032402
32006032403
42006032404
82006032408
--------------------------------------------
1.leftjoin
sql语句如下:
select*fromA
leftjoinB
onA.aID=B.bID
结果如下:
aIDaNumbIDbName
1a20050111 12006032401
2a20050112 22006032402
3a20050113 32006032403
4a20050114 42006032404
5a20050115 NULLNULL
〔所影响的行数为5行〕
结果说明:
leftjoin是以A表的记录为根底的,A可以看成左表,B可以看成右表,leftjoin是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为:
A.aID=B.bID).
B表记录不足的地方均为NULL.
--------------------------------------------
2.rightjoin
sql语句如下:
select*fromA
rightjoinB
onA.aID=B.bID
结果如下:
aIDaNumbIDbName
1a20050111 12006032401
2a20050112 22006032402
3a20050113 32006032403
4a20050114 42006032404
NULLNULL82006032408
〔所影响的行数为5行〕
结果说明:
仔细观察一下,就会发现,和leftjoin的结果刚好相反,这次是以右表(B)为根底的,A表不足的地方用NULL填充.
--------------------------------------------
3.innerjoin
sql语句如下:
select*fromA
innerjoinB
onA.aID=B.bID
结果如下:
aIDaNumbIDbName
1a20050111 12006032401
2a20050112 22006032402
3a20050113 32006032403
4a20050114 42006032404
结果说明:
很明显,这里只显示出了A.aID=B.bID的记录.这说明innerjoin并不以谁为根底,它只显示符合条件的记录.
--------------------------------------------
注:
LEFTJOIN操作用于在任何的FROM子句中,组合来源表的记录。
使用LEFTJOIN运算来创建一个左边外部联接。
左边外部联接将包含了从第一个〔左边〕开始的两个表中的全部记录,即使在第二个〔右边〕表中并没有相符值的记录。
语法:
FROMtable1LEFTJOINtable2ONtable1.field1compoprtable2.field2
说明:
table1,table2参数用于指定要将记录组合的表的名称。
field1,field2参数指定被联接的字段的名称。
且这些字段必须有一样的数据类型与包含一样类型的数据,但它们不需要有一样的名称。
compopr参数指定关系比拟运算符:
"=","<",">","<=",">="或"<>"。
如果在INNERJOIN操作中要联接包含Memo数据类型或OLEObject数据类型数据的字段,将会发生错误.
时间漩涡
用上面提到的A、B表为例:
select*fromA
leftjoin
(
selectbID,bNamefromBwherebID<=3
)T
onT.bID=A.aID
第2章innerjoin,leftjoin,rightjoin图形解析
现在有两表:
1.部门表(branch):
2.员工表(employee):
a.连接(innerjoin)
连接就是 获取两表共有的数据。
Sql代码
1.SELECT b.branch,e.name FROM branch AS b INNER JOIN employee AS e ON b.id=e.branchid
SELECTb.branch,e.nameFROMbranchASbINNERJOINemployeeASeONb.id=e.branchid
结果:
如果Sql换个写法:
Sql代码
1.SELECT b.branch,e.name FROM employee AS e INNER JOIN branch AS b ON b.id=e.branchid
SELECTb.branch,e.nameFROMemployeeASeINNERJOINbranchASbONb.id=e.branchid
结果:
呵呵,发现结果是一样的,不管连接的哪表,结果都是获取的两表的共有数据!
此外,还有一种写法,能达到同样的效果:
Sql代码
1.SELECT b.branch,e.name FROM employee AS e,branch AS b WHERE b.id=e.branchid
SELECTb.branch,e.nameFROMemployeeASe,branchASbWHEREb.id=e.branchid
结果:
b.左连接(leftjoin) :
Sql代码
1.SELECT b.branch,e.name FROM branch AS b LEFT JOIN employee AS e ON b.id=e.branchid
SELECTb.branch,e.nameFROMbranchASbLEFTJOINemployeeASeONb.id=e.branchid
结果:
分析下,为什么会有这样的结果,找个诀窍,看Sql语句的from关键字的后面是哪表(branch表),那么结果一定包含branch表中的所查询的字段(branch字段)所关联左表(employee表)的所有数据,如果左连接表(employee表中)不存在branch中的所查询字段,那么该字段的值为Null。
好了,现在换个写法:
Sql代码
1.SELECT b.branch,e.name FROM employee AS e LEFT JOIN branch AS b ON b.id=e.branchid
SELECTb.branch,e.nameFROMemployeeASeLEFTJOINbranchASbONb.id=e.branchid
结果:
在分析下,看Sql语句的from关键字的后面是哪表(employee表),那么结果一定包含employee表中的所查询的字段(name字段)所关联左表(branch表)的所有数据。
c.右连接(rightjoin) :
第3章INNERJOIN和leftJOIN
假设我们描述这样一种关系,表1存储的是区域和区域ID,表二代表的是微软公司的子公司以与子公司所在的区域
1、表1
CREATETABLE[region](
[regionid][int]IDENTITY(1,1)NOTNULL,
[regionname][varchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
CONSTRAINT[PK_region]PRIMARYKEYCLUSTERED
(
[regionid]
)ON[PRIMARY]
)ON[PRIMARY]
GO
2、假设我们描述这样一种关系
CREATETABLE[MicroSoft](
[subId][int]IDENTITY(1,1)NOTNULL,
[regionid][int]NOTNULL,
[name][varchar](50)null,
CONSTRAINT[PK_microsoft]PRIMARYKEYCLUSTERED
(
[subId]
)ON[PRIMARY]
)ON[PRIMARY]
GO
3.1现在比拟:
leftjoin和innerjoin
SELECT*FROMmicrosoftt1INNERJOINregiont2ONt1.regionid=t2.regionid
SELECT*FROMmicrosoftt1leftJOINregiont2ONt1.regionid=t2.regionid
由此可以看出,innerjoin联接使用比拟运算符根据每个表共有的列的值匹配两个表中的行。
左向外联接的结果集包括LEFTOUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。
如果左表的某行在右表中没有匹配行,那么在相关联的结果集行中右表的所有选择列表列均为空值。
右向外联接是左向外联接的反向联接。
将返回右表的所有行。
如果右表的某行在左表中没有匹配行,那么将为左表返回空值。
完整外部联接返回左表和右表中的所有行。
当某行在另一个表中没有匹配行时,那么另一个表的选择列表列包含空值。
如果表之间有匹配行,那么整个结果集行包含基表的数据值。
交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合。
交叉联接也称作笛卡尔积
第4章提升leftjoin效率
一:
from子句中过滤数据后leftjoin跟先leftjoin后过滤数据的执行效率比拟;
分别举例如下:
test1:
selectt1.emp_no,t1.emp_name,t2.dep_no,t2.dep_name
from(
selectt.emp_no,t.emp_name,t.dep_no
fromemployee
wheret.emp_no<80707999
)t1
leftjoindepartmentt2ont1.dep_no=t2.dep_no
test2:
selectt1.emp_no,t1.emp_name,t2.dep_no,t2.dep_name
fromemployeet1
leftjoindepartmentt2ont1.dep_no=t2.dep_no
wheret1.emp_no<80707999
在大数据量的情况下test1比test2效率高
第5章SQLServer中Innerjoin和where的效率差异
各大论坛,包括MSDN上很多人提出了这个问题,但回答是众说纷纭。
总体上总结出来时说:
对小数据量〔 还是自己来做试验吧。 如是有了如下比拟结果〔均在查询分析器中查询和计时〕: 语句〔1〕 declareoperatorNamenvarchar(50) setoperatorName='%' selectdistinctitem.*fromitem,customer_item,customer_operator,operator whereitem.itemcode=customer_item.itemCode andcustomer_item.customerCode=customer_operator.customerCode andcustomer_operator.operatorId=customer_operator.operatorId andoperator.operatorNamelikeoperatorName anditem.deleted=0andcustomer_item.deleted=0andcustomer_operator.deleted=0 查询结果,74行,共时间0: 00: 04 语句〔2〕 declareoperatorNamenvarchar(50) setoperatorName='%' selectdistinctitem.*fromiteminnerjoincustomer_item onitem.itemcode=customer_item.itemCode innerjoincustomer_operatoroncustomer_item.customerCode=customer_operator.customerCode innerjoinoperatoroncustomer_operator.operatorId=operator.operatorId whereoperator.operatorNamelikeoperatorName anditem.deleted=0andcustomer_item.deleted=0andcustomer_operator.deleted=0 共74行,时间0: 00: 01 后检查发现语句〔1〕中有一个重复自查询条件: customer_operator.operatorId=customer_operator.operatorId 将其叶加到语句2中,语句〔3〕 declareoperatorNamenvarchar(50) setoperatorName='%' selectdistinctitem.*fromiteminnerjoincustomer_item onitem.itemcode=customer_item.itemCode innerjoincustomer_operatoroncustomer_item.customerCode=customer_operator.customerCode innerjoinoperatoroncustomer_operator.operatorId=operator.operatorId whereoperator.operatorNamelikeoperatorName anditem.deleted=0andcustomer_item.deleted=0andcustomer_operator.deleted=0 andcustomer_operator.operatorId=customer_operator.operatorId 所用时间和结果都为74行,时间0: 00: 01。 将语句〔1〕中的去掉该条件后成为语句〔4〕 declareoperatorNamenvarchar(50) setoperatorName='%' selectdistinctitem.*fromitem,customer_item,customer_operator,operator whereitem.itemcode=customer_item.itemCode andcustomer_item.customerCode=customer_operator.customerCode --andcustomer_operator.operatorId=customer_operator.operatorId andoperator.operatorNamelikeoperatorName anditem.deleted=0andcustomer_item.deleted=0andcustomer_operator.deleted=0 时间和结果为74行,时间0: 00: 01。 终于发现了些他们的差异。 结论: 尽量使用Join而不是Where来列出关联条件,特别是多个表联合的时候。 原因是: 〔1〕在效率上,Where可能具有和Innerjoin一样的效率。 但根本可以肯定的〔通过SQLServer帮助和其它资料,以与本测试〕是Join的效率不比Where差。 〔2〕使用Join可以帮助检查语句中的无效或者误写的关联条件
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 语句 join 用法 效率 说明