关于inner jion, left jion ,right jion之间的执行性能，期望高手一起探讨

来源：互联网发布：php开发工程师培训编辑：程序博客网时间：2024/06/14 09:59

首先我们先创建两个表：

CREATE TABLE [dbo].[Roles](
[Name] [nvarchar](50) NOT NULL,
[CreatedAt] [datetime] NOT NULL,
[UpdatedAt] [datetime] NOT NULL,
[Deleted] [bit] NOT NULL,
[RoleStatus] [int] NOT NULL,
[ID] [uniqueidentifier] NOT NULL,
[SystemID] [uniqueidentifier] NOT NULL,
[RoleKind] [int] NOT NULL,
CONSTRAINT [PK_Roles] 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

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1=默认管理员角色，2=普通角色' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Roles', @level2type=N'COLUMN',@level2name=N'RoleKind'
GO

ALTER TABLE [dbo].[Roles] ADD CONSTRAINT [DF_Roles_Deleted] DEFAULT ((0)) FOR [Deleted]
GO

ALTER TABLE [dbo].[Roles] ADD CONSTRAINT [DF_Roles_RoleStatus] DEFAULT ((1)) FOR [RoleStatus]
GO

ALTER TABLE [dbo].[Roles] ADD CONSTRAINT [DF_Roles_RoleKind] DEFAULT ((2)) FOR [RoleKind]
GO

CREATE TABLE [dbo].[RoleFunctions](
[MappingID] [int] IDENTITY(1,1) NOT NULL,
[Status] [int] NOT NULL,
[RoleID] [uniqueidentifier] NOT NULL,
[FunctionID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_RoleFunctions] PRIMARY KEY CLUSTERED
(
[MappingID] 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

ALTER TABLE [dbo].[RoleFunctions] ADD CONSTRAINT [DF_RoleFunctions_Status] DEFAULT ((0)) FOR [Status]
GO

以下是两个条SQL查询语句，但是不同的是一个是用inner jion，一个是用left jion，但是两个查询语句的执行计划差距非常大

/****** Script for SelectTopNRows command from SSMS ******/
SELECT [MappingID]
,[Status]
,[RoleID]
,[FunctionID]
FROM [RoleFunctions]
left join roles on roles.id = rolefunctions.functionid

SELECT [MappingID]
,[Status]
,[RoleID]
,[FunctionID]
FROM [RoleFunctions]
inner join roles on roles.id = rolefunctions.functionid

期望高手给予讲解……