SQL点滴2—重温sql语句中的join操作

原文: SQL点滴2—重温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