MSSQL中 各种数据表连接的写法 [左连接，右连接......]

DROP TABLE [UserInfo]
DROP TABLE Score
GO
CREATE TABLE [dbo].[UserInfo](
	[rid] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[uname] [nvarchar](50) NULL,
	[usex] [int] NULL,
	[email] [nvarchar](50) NULL
)  

GO

CREATE TABLE Score(
ID INT IDENTITY(1,1) PRIMARY KEY,
UserId int ,
Subjec nvarchar(2),
Score  decimal(18,2)
)

GO

INSERT INTO UserInfo
VALUES('周笔畅',1,'192@1.com')
INSERT INTO UserInfo
VALUES('李宇春',1,'192@1.com')
INSERT INTO Score
VALUES(1,'语文',90)
INSERT INTO Score
VALUES(1,'数学',90)
INSERT INTO Score
VALUES(1,'英语',90)
INSERT INTO Score
VALUES(6,'英语',90)

SELECT * FROM UserInfo
SELECT * FROM Score

--内联接简单写法
SELECT * FROM UserInfo U ,Score S WHERE U.RID=S.UserId
SELECT * FROM UserInfo U JOIN Score S ON U.RID=S.UserId
SELECT * FROM UserInfo U INNER JOIN Score S ON U.RID=S.UserId

--左外连接
SELECT * FROM UserInfo U LEFT JOIN Score S ON U.RID=S.UserId
SELECT * FROM UserInfo U LEFT OUTER JOIN Score S ON U.RID=S.UserId

--右外连接
SELECT * FROM UserInfo U RIGHT JOIN Score S ON U.RID=S.UserId
SELECT * FROM UserInfo U RIGHT OUTER JOIN Score S ON U.RID=S.UserId

--完整外联接
SELECT * FROM UserInfo U FULL JOIN Score S ON U.RID=S.UserId
SELECT * FROM UserInfo U FULL OUTER JOIN Score S ON U.RID=S.UserId

--交叉联接
SELECT * FROM UserInfo U CROSS  JOIN Score S

--内联接简单写法

--左外连接

--右外连接

--完整外联接

--交叉联接