--关于连接的简单示例--测试数据
declare @a table(ida int)
insert @a select 1
union all select 2declare @b table(idb int)
insert @b select 2
union all select 3--内连接
select * from @a a join @b b on a.ida=b.idb/*--测试结果
只返回两个表中ida与idb相同的记录
ida idb
----------- -----------
2 2(所影响的行数为 1 行)
--*/
--左连接
select * from @a a left join @b b on a.ida=b.idb/*--测试结果
返回@a(左边表)所有的记录,及@b的idb与@a的ida对应的记录
没有对应的就用null表示ida idb
----------- -----------
1 NULL
2 2(所影响的行数为 2 行)
--*/
--右连接
select * from @a a right join @b b on a.ida=b.idb/*--测试结果
返回@b(右边表)所有的记录,及@b的idb与@a的ida对应的记录
没有对应的就用null表示ida idb
----------- -----------
2 2
NULL 3(所影响的行数为 2 行)
--*/
--全连接
select * from @a a full join @b b on a.ida=b.idb/*--测试结果
返回@a与@b的所有记录,没有对应的用NULL表示ida idb
----------- -----------
2 2
NULL 3
1 NULL(所影响的行数为 3 行)
--*/
declare @a table(ida int)
insert @a select 1
union all select 2declare @b table(idb int)
insert @b select 2
union all select 3--内连接
select * from @a a join @b b on a.ida=b.idb/*--测试结果
只返回两个表中ida与idb相同的记录
ida idb
----------- -----------
2 2(所影响的行数为 1 行)
--*/
--左连接
select * from @a a left join @b b on a.ida=b.idb/*--测试结果
返回@a(左边表)所有的记录,及@b的idb与@a的ida对应的记录
没有对应的就用null表示ida idb
----------- -----------
1 NULL
2 2(所影响的行数为 2 行)
--*/
--右连接
select * from @a a right join @b b on a.ida=b.idb/*--测试结果
返回@b(右边表)所有的记录,及@b的idb与@a的ida对应的记录
没有对应的就用null表示ida idb
----------- -----------
2 2
NULL 3(所影响的行数为 2 行)
--*/
--全连接
select * from @a a full join @b b on a.ida=b.idb/*--测试结果
返回@a与@b的所有记录,没有对应的用NULL表示ida idb
----------- -----------
2 2
NULL 3
1 NULL(所影响的行数为 3 行)
--*/
在ORACLE中:
连接有三种类型
散列连接
合并连接
嵌套循环连接。
当在一个程序段中执行到要执行连接时,会显示下列其中一个语句:
Hash Join
或
Merge Join
或
Nested Loop Join
nested loop join 一般适和大小表,且大表有index,全表scan小表,然后按index抽取大表匹配记录,返会第一条记录快。merger join 要对两表排序,然后匹配,要等所有记录处理完后,才能返回结果。hash join 要对一表计算hash值,然后对另一表联接自段算hash,找到匹配记录。
-----详细内容发到ORACLE版块中去
一般适和大小表,且大表有index,全表scan小表,然后按index抽取大表匹配记录,返会第一条记录快。
inner loop joininner 可以省略,代表默认的内联接
loop 是sql server的连接提示, 表明使用 嵌套循环联接(nested loop join)相关内容可以查询帮助 “提示, 联接提示”
Merge joins
Hash joins
If one join input is quite small (such as fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, index nested loops are the fastest join operation because they require the least I/O and the fewest comparisons. For more information about nested loops, see Understanding Nested Loops Joins. If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), merge join is the fastest join operation. If both join inputs are large and the two inputs are of similar sizes, merge join with prior sorting and hash join offer similar performance. However, hash join operations are often much faster if the two input sizes differ significantly from each other. For more information, see Understanding Merge Joins. Hash joins can process large, unsorted, nonindexed inputs efficiently. They are useful for intermediate results in complex queries because: Intermediate results are not indexed (unless explicitly saved to disk and then indexed) and often are not produced suitably sorted for the next operation in the query plan.
Query optimizers estimate only intermediate result sizes. Because estimates can be an order of magnitude wrong in complex queries, algorithms to process intermediate results not only must be efficient but also must degrade gracefully if an intermediate result turns out to be much larger than anticipated.
The hash join allows reductions in the use of denormalization to occur. Denormalization is typically used to achieve better performance by reducing join operations, in spite of the dangers of redundancy, such as inconsistent updates. Hash joins reduce the need to denormalize. Hash joins allow vertical partitioning (representing groups of columns from a single table in separate files or indexes) to become a viable option for physical database design. For more information, see Understanding Hash Joins.