SQL Server internal connection, left outer join, right outer join, full connection, cross Lianjie

Data preparation:

Create TableT1 (Avarchar(Ten) not NULL, Bvarchar(Ten) not NULL, Ctinyint  not NULL);Create TableT2 (Bvarchar(Ten) not NULL, Etinyint  not NULL);Insert  intoT1Values('A1','B1',5),('A1','B2',6),('A2','B3',8),('A2','B4', A);Insert  intoT2Values('B1',3),('B2',7),('B3',Ten),('B3',2),('b5',2);Select *  fromT1;Select *  fromT2;

Results:

1 A B C2 ---------- ---------- ----3A1 B154A1 B265A2 B386A2 B4 A7 8(4row affected)9 Ten B E One ---------- ---- AB13 -B27 -B3Ten theB32 -B52 -  -(5Row affected)

1. Internal connection

1 Select *  fromT1Inner JoinT2 onT1. B=t2.b2 3 A b C B E4 ---------- ---------- ---- ---------- ----5A1 B15B136A1 B26B277A2 B38B3Ten8A2 B38B32

2. Left Outer connection

1 Select *  fromT1 Left outer JoinT2 onT1. B=t2.b2 3 A b C B E4 ---------- ---------- ---- ---------- ----5A1 B15B136A1 B26B277A2 B38B3Ten8A2 B38B329A2 B4 A   NULL       NULL

3. Right outer connection

1 Select *  fromT1 Right outer JoinT2 onT1. B=t2.b2 3 A b C B E4 ---------- ----------   ---- ---------- ----5A1 B15B136A1 B26B277A2 B38B3Ten8A2 B38B329 NULL     NULL     NULLB52

4. Fully connected

1 Select *  fromT1 Full outer JoinT2 onT1. B=t2.b2 3 A b C B E4 ---------- ---------- ---- ---------- ----5A1 B15B136A1 B26B277A2 B38B3Ten8A2 B38B329A2 B4 A     NULL      NULLTen NULL       NULL       NULLB52

5. Cross connection (Cartesian product)

1 Select *  fromT1 Cross JoinT22 3 A b C B E4 ---------- ---------- ---- ---------- ----5A1 B15B136A1 B15B277A1 B15B3Ten8A1 B15B329A1 B15B52TenA1 B26B13 OneA1 B26B27 AA1 B26B3Ten -A1 B26B32 -A1 B26B52 theA2 B38B13 -A2 B38B27 -A2 B38B3Ten -A2 B38B32 +A2 B38B52 -A2 B4 AB13 +A2 B4 AB27 AA2 B4 AB3Ten atA2 B4 AB32 -A2 B4 AB52

SQL Server internal connection, left outer join, right outer join, full connection, cross Lianjie