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

结果：

 1 A          B          C
 2 ---------- ---------- ----
 3 a1         b1         5
 4 a1         b2         6
 5 a2         b3         8
 6 a2         b4         12
 7 
 8 (4 行受影响)
 9 
10 B          E
11 ---------- ----
12 b1         3
13 b2         7
14 b3         10
15 b3         2
16 b5         2
17 
18 (5 行受影响)

1、内连接

1 select * from T1 inner join T2 on T1.B = T2.B
2 
3 A          B          C    B          E
4 ---------- ---------- ---- ---------- ----
5 a1         b1         5    b1         3
6 a1         b2         6    b2         7
7 a2         b3         8    b3         10
8 a2         b3         8    b3         2

2、左外连接

1 select * from T1 left outer join T2 on T1.B = T2.B
2 
3 A          B          C    B          E
4 ---------- ---------- ---- ---------- ----
5 a1         b1         5    b1         3
6 a1         b2         6    b2         7
7 a2         b3         8    b3         10
8 a2         b3         8    b3         2
9 a2         b4         12   NULL       NULL

3、右外连接

1 select * from T1 right outer join T2 on T1.B = T2.B
2 
3 A          B          C      B          E
4 ---------- ----------   ---- ---------- ----
5 a1         b1         5      b1         3
6 a1         b2         6      b2         7
7 a2         b3         8      b3         10
8 a2         b3         8      b3         2
9 NULL     NULL     NULL b5         2

4、完全连接

 1 select * from T1 full outer join T2 on T1.B = T2.B
 2 
 3 A            B            C       B          E
 4 ---------- ---------- ---- ---------- ----
 5 a1           b1           5       b1         3
 6 a1           b2           6       b2         7
 7 a2           b3           8       b3         10
 8 a2           b3           8       b3         2
 9 a2           b4           12     NULL      NULL
10 NULL       NULL       NULL  b5         2

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

 1 select * from T1 cross join T2
 2 
 3 A          B          C    B          E
 4 ---------- ---------- ---- ---------- ----
 5 a1         b1         5    b1         3
 6 a1         b1         5    b2         7
 7 a1         b1         5    b3         10
 8 a1         b1         5    b3         2
 9 a1         b1         5    b5         2
10 a1         b2         6    b1         3
11 a1         b2         6    b2         7
12 a1         b2         6    b3         10
13 a1         b2         6    b3         2
14 a1         b2         6    b5         2
15 a2         b3         8    b1         3
16 a2         b3         8    b2         7
17 a2         b3         8    b3         10
18 a2         b3         8    b3         2
19 a2         b3         8    b5         2
20 a2         b4         12   b1         3
21 a2         b4         12   b2         7
22 a2         b4         12   b3         10
23 a2         b4         12   b3         2
24 a2         b4         12   b5         2

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

标签：class   color   sql   arch   null   sel   cross   font   div