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