在SQL Server中，合并和合并联接是否相同? [英] Is Merge and Merge join same in SQL Server?

问题描述

SQL Server中的合并和合并联接有什么区别?

解决方案

MERGE 是DML语句(数据操作语言).
也称为UPSERT(更新插入).
它会尝试根据您定义的条件将源(表/视图/查询)与目标(表/可更新视图)进行匹配，然后根据匹配结果将行插入/更新/删除目标表中的行. br> 合并(Transact-SQL)

create table src (i int, j int);
create table trg (i int, j int);

insert into src values (1,1),(2,2),(3,3);
insert into trg values (2,20),(3,30),(4,40);

merge into  trg
using       src
on          src.i = trg.i
when not matched by target then insert (i,j) values (src.i,src.j)
when not matched by source then update set trg.j = -1
when matched then update set trg.j = trg.j + src.j
;

select * from trg order by i

+---+----+
| i | j  |
+---+----+
| 1 | 1  |
+---+----+
| 2 | 22 |
+---+----+
| 3 | 33 |
+---+----+
| 4 | -1 |
+---+----+

MERGE JOIN 是一种联接算法(例如HASH JOIN或NESTED LOOPS).
它是基于首先根据联接条件对两个数据集进行排序(由于存在索引而可能已经排序)，然后遍历排序后的数据集并找到匹配项.

create table t1 (i int)
create table t2 (i int)

select * from t1 join t2 on t1.i = t2.i option (merge join)

create table t1 (i int primary key)
create table t2 (i int primary key)

select * from t1 join t2 on t1.i = t2.i option (merge join)

在SQL Server中，主键表示聚集索引结构，这意味着表存储为B-Tree，并按主键排序.

了解合并联接

What is the difference between Merge and a Merge Join in SQL Server?

解决方案

MERGE is a DML statement (data manipulation language).
Also called UPSERT (Update-Insert).
It tries to match source (table / view / query) to a target (table / updatable view) based on your defined conditions and then based on the matching results it insert/update/delete rows to/in/of the target table.
MERGE (Transact-SQL)

create table src (i int, j int);
create table trg (i int, j int);

insert into src values (1,1),(2,2),(3,3);
insert into trg values (2,20),(3,30),(4,40);

merge into  trg
using       src
on          src.i = trg.i
when not matched by target then insert (i,j) values (src.i,src.j)
when not matched by source then update set trg.j = -1
when matched then update set trg.j = trg.j + src.j
;

select * from trg order by i

+---+----+
| i | j  |
+---+----+
| 1 | 1  |
+---+----+
| 2 | 22 |
+---+----+
| 3 | 33 |
+---+----+
| 4 | -1 |
+---+----+

MERGE JOIN is a join algorithm (e.g. HASH JOIN or NESTED LOOPS).
It is based on first sorting both datasets according to the join conditions (maybe already sorted due to index existent) and then traversing through the sorted datasets and finding matches.

create table t1 (i int)
create table t2 (i int)

select * from t1 join t2 on t1.i = t2.i option (merge join)

create table t1 (i int primary key)
create table t2 (i int primary key)

select * from t1 join t2 on t1.i = t2.i option (merge join)

In SQL Server a primary key implies clustered index structure which means the table is stored as a B-Tree, sorted by the primary key.

Understanding Merge Joins

这篇关于在SQL Server中，合并和合并联接是否相同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！