SQL Server内连接、左外连接、右外连接、完全连接、交叉lianjie

数据准备：

create table T1(
A varchar(10) not null,
B varchar(10) not null,
C tinyint not null
);

create table T2(
B varchar(10) not null,
E tinyint not null
);


insert into T1
values
('a1', 'b1', 5),
('a1', 'b2', 6),
('a2', 'b3', 8),
('a2', 'b4', 12);

insert into T2
values
('b1', 3),
('b2', 7),
('b3', 10),
('b3', 2),
('b5', 2);

select * from T1;
select * from T2;

结果：

A          B          C
---------- ---------- ----
a1         b1         5
a1         b2         6
a2         b3         8
a2         b4         12

(4 行受影响)

B          E
---------- ----
b1         3
b2         7
b3         10
b3         2
b5         2

(5 行受影响)

1、内连接

select * from T1 inner join T2 on T1.B = T2.B

A          B          C    B          E
---------- ---------- ---- ---------- ----
a1         b1         5    b1         3
a1         b2         6    b2         7
a2         b3         8    b3         10
a2         b3         8    b3         2

2、左外连接

select * from T1 left outer join T2 on T1.B = T2.B

A          B          C    B          E
---------- ---------- ---- ---------- ----
a1         b1         5    b1         3
a1         b2         6    b2         7
a2         b3         8    b3         10
a2         b3         8    b3         2
a2         b4         12   NULL       NULL

3、右外连接

select * from T1 right outer join T2 on T1.B = T2.B

A          B          C      B          E
---------- ----------   ---- ---------- ----
a1         b1         5      b1         3
a1         b2         6      b2         7
a2         b3         8      b3         10
a2         b3         8      b3         2
NULL     NULL     NULL b5         2

4、完全连接

select * from T1 full outer join T2 on T1.B = T2.B

A            B            C       B          E
---------- ---------- ---- ---------- ----
a1           b1           5       b1         3
a1           b2           6       b2         7
a2           b3           8       b3         10
a2           b3           8       b3         2
a2           b4           12     NULL      NULL
NULL       NULL       NULL  b5         2

5、交叉连接（笛卡尔乘积）

select * from T1 cross join T2

A          B          C    B          E
---------- ---------- ---- ---------- ----
a1         b1         5    b1         3
a1         b1         5    b2         7
a1         b1         5    b3         10
a1         b1         5    b3         2
a1         b1         5    b5         2
a1         b2         6    b1         3
a1         b2         6    b2         7
a1         b2         6    b3         10
a1         b2         6    b3         2
a1         b2         6    b5         2
a2         b3         8    b1         3
a2         b3         8    b2         7
a2         b3         8    b3         10
a2         b3         8    b3         2
a2         b3         8    b5         2
a2         b4         12   b1         3
a2         b4         12   b2         7
a2         b4         12   b3         10
a2         b4         12   b3         2
a2         b4         12   b5         2