用实例展示left Join,right join,inner join,join,cross join,union 的区别

1.向TI,T2插入数据：

T1  7条

ID Field2 Field3 Field4
1 1 3 54
2 1 3 54
3 1 3 54
4 2 3 54
5 3 3 54
6 4 3 54
7 5 3 54

 

T2  7条

IDField22Field23

1133

2133

3133

4133

5233

6333

7   9   33

 

2.测试left join

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 left join dbo.T2 on t1.Field2=t2.Field22

row_numIDField2Field3Field4IDField22Field23

1113541133

2113542133

3113543133

4113544133

5213541133

6213542133

7213543133

8213544133

9313541133

10313542133

11313543133

12313544133

13423545233

14533546333

1564354NULLNULLNULL

1675354NULLNULLNULL

 

3.测试right join

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 right join dbo.T2 on t1.Field2=t2.Field22

row_numIDField2Field3Field4IDField22Field23

1NULLNULLNULLNULL8933

2113541133

3113542133

4113543133

5113544133

6213541133

7213542133

8213543133

9213544133

10313541133

11313542133

12313543133

13313544133

14423545233

15533546333

 

4.测试join

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 join dbo.T2 on t1.Field2=t2.Field22

row_numIDField2Field3Field4IDField22Field23

1113541133

2113542133

3113543133

4113544133

5213541133

6213542133

7213543133

8213544133

9313541133

10313542133

11313543133

12313544133

13423545233

14533546333

 

5.测试inner join

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 inner join dbo.T2 on t1.Field2=t2.Field22

row_numIDField2Field3Field4IDField22Field23

1113541133

2113542133

3113543133

4113544133

5213541133

6213542133

7213543133

8213544133

9313541133

10313542133

11313543133

12313544133

13423545233

14533546333

 

6.测试cross join

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 cross join dbo.T2 on t1.Field2=t2.Field22

消息156，级别15，状态1，第1 行

关键字'on' 附近有语法错误。

 

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 cross join dbo.T2 

row_numIDField2Field3Field4IDField22Field23

1113541133

2113542133

3113543133

4113544133

5113545233

6113546333

7113548933

8213541133

9213542133

10213543133

11213544133

12213545233

13213546333

14213548933

15313541133

16313542133

17313543133

18313544133

19313545233

20313546333

21313548933

22423541133

23423542133

24423543133

25423544133

26423545233

27423546333

28423548933

29533541133

30533542133

31533543133

32533544133

33533545233

34533546333

35533548933

36643541133

37643542133

38643543133

39643544133

40643545233

41643546333

42643548933

43753541133

44753542133

45753543133

46753544133

47753545233

48753546333

49753548933

 

7.测试unoin

select * from dbo.T1 union all select * from dbo.T2

消息205，级别16，状态1，第1 行

使用UNION、INTERSECT 或EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。

 

select Field2 from dbo.T1 union all select Field22 from dbo.T2

Field2

1

1

1

2

3

4

5

1

1

1

1

2

3

9

 

with T3 as

( select Field2 from dbo.T1 union all select Field22 from dbo.T2 )

select ROW_NUMBER() over (order by T3.field2) as row_num,* from T3

 

row_numField2

11

21

31

41

51

61

71

82

92

103

113

124

135

149

 

8.生成数据库的脚本

USE [Demo]

GO

 

/****** Object:  Table [dbo].[T1]    Script Date: 09/27/2013 23:19:29 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[T1](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Field2] [int] NULL,

[Field3] [int] NULL,

[Field4] [int] NULL,

 CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

USE [Demo]

GO

 

/****** Object:  Table [dbo].[T2]    Script Date: 09/27/2013 23:19:53 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[T2](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Field22] [int] NOT NULL,

[Field23] [int] NOT NULL,

 CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

 

delete from dbo.T1

truncate table dbo.T2

 

select * from  T1

select * from  T2

 

insert dbo.T1 values (1,3,54)

insert dbo.T1 values (1,3,54)

insert dbo.T1 values (1,3,54)

insert dbo.T1 values (2,3,54)

insert dbo.T1 values (3,3,54)

insert dbo.T1 values (4,3,54)

insert dbo.T1 values (5,3,54)

 

truncate table dbo.T2

insert dbo.T2 values (1,33)

insert dbo.T2 values (1,33)

insert dbo.T2 values (1,33)

insert dbo.T2 values (1,33)

insert dbo.T2 values (2,33)

insert dbo.T2 values (3,33)

insert dbo.T2 values (4,33)

insert dbo.T2 values (9,33)

 

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 left join dbo.T2 on t1.Field2=t2.Field22

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 right join dbo.T2 on t1.Field2=t2.Field22

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 join dbo.T2 on t1.Field2=t2.Field22

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 inner join dbo.T2 on t1.Field2=t2.Field22

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 cross join dbo.T2 on t1.Field2=t2.Field22

select ROW_NUMBER()over (order by t1.id,t2.id) as row_num,* from dbo.T1 cross join dbo.T2 

 

select * from dbo.T1 union all select * from dbo.T2

 

select Field2 from dbo.T1 union all select Field22 from dbo.T2

 

with T3 as

( select Field2 from dbo.T1 union all select Field22 from dbo.T2 )

select ROW_NUMBER() over (order by T3.field2) as row_num,* from T3