SqlServer关于join的用法说明http://hi.baidu.com/yemingsiji/blog/item/c96837fa5ce6a08358ee9010.html
2011-03-03 22:20
|
SqlServer关于join的用法： 一直对join的连接用法迷惑，网上搜到了比较通俗的解释： 1. 连接类型 SQL提供了多种类型的连接方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。 Ø 内连接 （Inner Join）： 只连接匹配的行 Ø 左外连接 （Left Outer Jion或Left Jion）： 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行 Ø 右外连接 (Rigt Outer Jion或Rigt Jion)： 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行 Ø 全外连接(Full Outer) ： 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。 Ø (H)(theta)连接 ： 使用等值以外的条件来匹配左、右两个表中的行 Ø 交叉连接： 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配 2.. 外连接与条件配合使用 当在内连接查询中加入条件是，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况就不同了。当把条件加入到 join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。如果将条件放到where子句中，SQL Server将会首先进行连接操作，然后使用where子句对连接后的行进行筛选。下面的两个查询展示了条件放置位子对执行结果的影响： 条件在join子句 select *from t_institution Ileft outer join t_teller ton i.inst_no =t.inst_no and i.inst_no = “5801” 结果是： inst_no inst_name inst_no teller_no teller_name 5801 天河区 5801 0001tom 5801 天河区 5801 0002david 5802 越秀区 5803 白云区 条件在where子句 select *from t_institution Ileft outer join t_teller ton i.inst_no =t.inst_no where i.inst_no = “5801” 结果是： inst_no inst_name inst_no teller_no teller_name 5801 天河区 5801 0001tom 5801 天河区 5801 0002david 3. 自身连接 自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据。例如人力资源数据库中雇员与老板的关系。 下面例子是在机构表中查找本机构和上级机构的信息。 selects.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_name fromt_institution i joint_institution s on i.superior_inst =s.inst_no 结果是： superior_inst sup_inst_name inst_no inst_name 800 广州市 5801 天河区 800 广州市 5802 越秀区 800 广州市 5803 白云区 4. 交叉(无限制) 连接 交叉连接用于对两个源表进行纯关系代数的乘运算。它不使用连接条件来限制结果集合，而是将分别来自两个数据源中的行以所有可能的方式进行组合。数据集合中一的每个行都要与数据集合二中的每一个行分别组成一个新的行。例如，如果第一个数据源中有5个行，而第二个数据源中有4个行，那么在它们之间进行交叉连接就会产生20个行。人们将这种类型的结果集称为笛卡尔乘积。 大多数交叉连接都是由于错误操作而造成的；但是它们却非常适合向数据库中填充例子数据，或者预先创建一些空行以便为程序执行期间所要填充的数据保留空间。 select * fromt_institution i cross join t_teller t 在交叉连接中没有on条件子句
|
图解SQL的Joinhttp://www.2cto.com/database/201109/104505.html
假设我们有两张表。
Table A是左边的表。
Table B是右边的表。
其各有四条记录，其中有两条记录是相同的，如下所示：
Table A:
|
id |
Name |
|
1 |
Pirate |
|
2 |
Monkey |
|
3 |
Ninja |
|
4 |
Spaghetti |
Table B:
|
id |
name |
|
1 |
Rutabaga |
|
2 |
Pirate |
|
3 |
Darth Vader |
|
4 |
Ninja |
下面让我们来看看不同的Join会产生什么样的结果。
|
Sql代码 1 SELECT* FROM TableA 2 INNERJOIN TableB 3 ONTableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 3 Ninja 4 Ninja Inner join 产生的结果集中，是A和B的交集。 |
|
|
Sql代码 4 SELECT* FROM TableA 5 FULLOUTER JOIN TableB 6 ONTableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader Full outer join 产生A和B的并集。但是需要注意的是，对于没有匹配的记录，则会以null做为值。 |
|
|
Sql代码 7 SELECT* FROM TableA 8 LEFT OUTER JOIN TableB 9 ONTableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null Left outer join 产生表A的完全集，而B表中匹配的则有值，没有匹配的则以null值取代。 |
|
|
Java代码 10 SELECT * FROM TableA 11 LEFT OUTER JOIN TableB 12 ON TableA.name = TableB.name 13 WHERE TableB.id IS null id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null 产生在A表中有而在B表中没有的集合。 |
|
|
Sql代码 14 SELECT* FROM TableA 15 FULLOUTER JOIN TableB 16 ONTableA.name = TableB.name 17 WHERETableA.id IS null 18 OR TableB.id IS null id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader 产生A表和B表都没有出现的数据集。 |
还需要注册的是我们还有一个是“交差集” cross join , 这种Join没有办法用文式图表示，因为其就是把表A和表B的数据进行一个N*M的组合，即笛卡尔积。表达式如下： Java代码 SELECT * FROM TableA CROSS JOIN TableB 这个笛卡尔乘积会产生 4 x 4 = 16 条记录，一般来说，我们很少用到这个语法。但是我们得小心，如果不是使用嵌套的select语句，一般系统都会产生笛卡尔乘积然再做过滤。这是对于性能来说是非常危险的，尤其是表很大的时候。
作者“ITStar”
SQL Server 中Inner join 和where的效率差异
2007-03-09 14:33:16 来源: 网奇 网友评论 1 条 进入论坛
天，手头上正在作的一个项目，在生成报表时，客户感觉太慢，于是，各处检查，看可否提示效率。界面上的都改进了，提升不大。如是在SQL 语句上下功夫。（我这人比较懒，对简单的语句和查询都没有经过仔细优化的，一般只对姚使用left join，outer join，group by 以及carsor的语句会仔细写并用数据库理论考虑和检查---因为这种语句一般测试时如果发现错误，检查和调试很麻烦）
先在网上Google搜索“Join 与 Where 效率”以及察看SQL Server 帮助文档，希望能获得“捷径”些的优化思路。
搜索的结果是，各大论坛，包括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 ,operator
where item.itemcode = customer_item.itemCode
and customer_item.customerCode = customer_operator.customerCode
and customer_operator.operatorId = customer_operator.operatorId
and operator.operatorName like @operatorName
and 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.customerCode
inner join operator on customer_operator.operatorId = operator.operatorId
where operator.operatorName like @operatorName
and 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.customerCode
inner join operator on customer_operator.operatorId = operator.operatorId
where operator.operatorName like @operatorName
and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0
and customer_operator.operatorId = customer_operator.operatorId
所用时间和结果都为74行，时间0：00：01。
将语句（1）中的去掉该条件后成为语句（4）
declare @operatorName nvarchar(50)
set @operatorName = '%'
select distinct item.* from item , customer_item , customer_operator ,operator
where item.itemcode = customer_item.itemCode
and customer_item.customerCode = customer_operator.customerCode
--and customer_operator.operatorId = customer_operator.operatorId
and operator.operatorName like @operatorName
and 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可以帮助检查语句中的无效或者误写的关联条件
