SQL join 笔记

DROP TABLE [dbo].[test_a]
GO
CREATE TABLE [dbo].[test_a] (
[id] int NULL ,
[name] nvarchar(255) NULL
)
GO
/*
 Records of test_a
*/
INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'1', N'蘋果')
INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'2', N'橘子')
INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'3', N'菠蘿')
INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'4', N'香蕉')
INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'5', N'西瓜')
GO
———————————————————–
DROP TABLE [dbo].[test_b]
GO
CREATE TABLE [dbo].[test_b] (
[id] int NULL ,
[name] nvarchar(255) NULL
)
GO
/*
 Records of test_b
*/
INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'1', N'梨子')
INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'2', N'蘋果')
INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'3', N'草莓')
INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'4', N'桃子')
INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'5', N'香蕉')
GO

先建立一下资料
接下来各种JOIN都来一下

/* inner join */
SELECT * FROM  test_a  INNER JOIN test_b ON test_a.name = test_b.name

/* outer join */
SELECT * FROM  test_a  left JOIN test_b ON test_a.name = test_b.name
SELECT * FROM  test_a  left JOIN test_b ON test_a.name = test_b.name WHERE  test_b.name IS NULL
SELECT * FROM  test_a  right JOIN test_b ON test_a.name = test_b.name
SELECT * FROM  test_a  right JOIN test_b ON test_a.name = test_b.name WHERE  test_a.name IS NULL
SELECT * FROM  test_a  FULL   JOIN test_b ON test_a.name = test_b.name
SELECT * FROM  test_a  FULL   JOIN test_b ON test_a.name = test_b.name WHERE  test_b.name IS NULL

/* cross join 沒事不要下 */
SELECT * FROM  test_a  cross JOIN test_b