SQL join 笔记

点击(此处)折叠或打开

1. DROP TABLE [dbo].[test_a]
   
2. GO
   
3. CREATE TABLE [dbo].[test_a] (
   
4. [id] int NULL ,
   
5. [name] nvarchar(255) NULL
   
6. )
   
7. GO
   
8. /*
   
9.  Records of test_a
   
10. */
    
11. INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'1', N'蘋果')
    
12. INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'2', N'橘子')
    
13. INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'3', N'菠蘿')
    
14. INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'4', N'香蕉')
    
15. INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'5', N'西瓜')
    
16. GO
    
17. ———————————————————–
    
18. DROP TABLE [dbo].[test_b]
    
19. GO
    
20. CREATE TABLE [dbo].[test_b] (
    
21. [id] int NULL ,
    
22. [name] nvarchar(255) NULL
    
23. )
    
24. GO
    
25. /*
    
26.  Records of test_b
    
27. */
    
28. INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'1', N'梨子')
    
29. INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'2', N'蘋果')
    
30. INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'3', N'草莓')
    
31. INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'4', N'桃子')
    
32. INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'5', N'香蕉')
    
33. GO

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

点击(此处)折叠或打开

1. /* inner join */
   
2. SELECT * FROM test_a INNER JOIN test_b ON test_a.name = test_b.name
   
3. 
   
4. /* outer join */
   
5. SELECT * FROM test_a left JOIN test_b ON test_a.name = test_b.name
   
6. SELECT * FROM test_a left JOIN test_b ON test_a.name = test_b.name WHERE test_b.name IS NULL
   
7. SELECT * FROM test_a right JOIN test_b ON test_a.name = test_b.name
   
8. SELECT * FROM test_a right JOIN test_b ON test_a.name = test_b.name WHERE test_a.name IS NULL
   
9. SELECT * FROM test_a FULL JOIN test_b ON test_a.name = test_b.name
   
10. SELECT * FROM test_a FULL JOIN test_b ON test_a.name = test_b.name WHERE test_b.name IS NULL
    
11. 
    
12. /* cross join 沒事不要下 */
    
13. SELECT * FROM test_a cross JOIN test_b