重温sql语句中的join操作

1.join语句

Sql join语句用来合并两个或多个表中的记录。ANSI标准SQL语句中有四种JOIN：INNER，OUTER，LEFTER，RIGHT，一个表或视图也可以可以和它自身做JOIN操作。下面举例说明。下面所举的例子使用的表有Employee和Department，在这两个表中Department.DepartmentID是主键，Employee.DepartmentID是外键。

图1—Employee表

LastName	DepartmentID	Country	EmployeeID
John	NULL	Germany	306
Jones	33	Australia	124
Rafferty	31	Australia	123
Robinson	34	United States	201
Smith	34	Germany	305

 图2—Department表

 

DepartmentID	DepartmentName
31	Sales
33	Engineering
34	Clerical
35	Marketing

下面是建表和数据语句：

USE [AppressFinancial]
GO
/****** Object:  Table [dbo].[department]    Script Date: 08/11/2010 15:18:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[department](
    [DepartmentID] [int] NULL,
    [DepartmentName] [varchar](25) NULL,
UNIQUE NONCLUSTERED 
(
    [DepartmentID] 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
SET ANSI_PADDING OFF
GO

USE [AppressFinancial]
GO
/****** Object:  Table [dbo].[department]    Script Date: 08/11/2010 15:18:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[department](
    [DepartmentID] [int] NULL,
    [DepartmentName] [varchar](25) NULL,
UNIQUE NONCLUSTERED 
(
    [DepartmentID] 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
SET ANSI_PADDING OFF
GO