Khác biệt giữa Inner Join (left join) và Outer Join (right join) trong SQL server

1. Inner join hay còn gọi là left join

• Lấy tất cả các bảng bên trái (Products) -> kết nối với bảng bên phải (Categories)
• Cột nào bên trái (Products) có giá trị mới lấy kết nối qua cột bên phải (Categories)

Mã:

select c.NameVN 'Category',p.Name 'Product'
from Products p
   join Categories c on c.Id=p.CategoryId

2. Outer join hay còn gọi là right join

• Lấy tất cả các bảng bên phải (Categories) -> kết nối với bảng bên trái (Products)
• Cột nào không có ở bên trái (Products) mà bên phải (Categories) có thì cho giá trị Null

Mã:

select c.NameVN 'Category',p.Name 'Product'
from Products p
   right join Categories c on c.Id=p.CategoryId

Lấy ví dụ dưới để hiểu rõ hơn:

• Chạy code sau để tạo database và table ví dụ

Mã:

CREATE DATABASE [EShopV103]
GO
USE [EShopV103]
GO
/****** Object:  Table [dbo].[Customers]  Script Date: 07/04/2014 08:14:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Categories](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Name] [nvarchar](50) NOT NULL,
   [NameVN] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
   [Id] 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
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Mã loại' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Categories', @level2type=N'COLUMN',@level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tên loại hàng' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Categories', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tên tiếng Việt' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Categories', @level2type=N'COLUMN',@level2name=N'NameVN'
GO
SET IDENTITY_INSERT [dbo].[Categories] ON
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1000, N'Watches', N'Đồng hồ đeo tay')
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1001, N'Laptops', N'Máy tính xách tay')
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1002, N'Cameras', N'Máy ảnh')
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1003, N'Mobiles', N'Điện thoại')
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1004, N'Perfumes', N'Nước hoa')
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1005, N'Jewels', N'Nữ trang')
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1006, N'Fashion Boats', N'Giày thời trang')
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1007, N'Travel Bags', N'Túi xách du lịch')
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1017, N'Hello', N'Chào')
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1018, N'Hello', N'Chào')
INSERT [dbo].[Categories] ([Id], [Name], [NameVN]) VALUES (1019, N'Hello', N'Chào')
SET IDENTITY_INSERT [dbo].[Categories] OFF
/****** Object:  Table [dbo].[Products]  Script Date: 07/04/2014 08:14:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Name] [nvarchar](60) NOT NULL,
   [UnitPrice] [float] NOT NULL,
   [Image] [nvarchar](50) NOT NULL,
   [ProductDate] [date] NOT NULL,
   [Available] [bit] NOT NULL,
   [CategoryId] [int] NOT NULL,
   [Quantity] [int] NOT NULL,
   [Description] [nvarchar](max) NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
   [Id] 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
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Mã hàng hóa' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Products', @level2type=N'COLUMN',@level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tên hàng hóa' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Products', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Đơn giá' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Products', @level2type=N'COLUMN',@level2name=N'UnitPrice'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Hình ảnh' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Products', @level2type=N'COLUMN',@level2name=N'Image'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ngày sản xuất' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Products', @level2type=N'COLUMN',@level2name=N'ProductDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Đang kinh doanh ?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Products', @level2type=N'COLUMN',@level2name=N'Available'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Mã loại, FK' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Products', @level2type=N'COLUMN',@level2name=N'CategoryId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Mô tả hàng hóa' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Products', @level2type=N'COLUMN',@level2name=N'Description'
GO
SET IDENTITY_INSERT [dbo].[Products] ON
INSERT [dbo].[Products] ([Id], [Name], [UnitPrice], [Image], [ProductDate], [Available], [CategoryId], [Quantity], [Description]) VALUES (1018, N'Carnarvon Tigers', 62.5, N'1018.jpg', CAST(0x1F340B00 AS Date), 1, 1007, 1, N'EmEditor uses JavaScript or VBScript for its macro language, so those who are familiar with HTML or Windows scripting will be able to write macros with little difficulty. For those unfamiliar with scripting languages, EmEditor can record keystrokes, which can then be saved in a macro file that can easily be loaded in different situations. With the use of JavaScript or VBScript, you can also troubleshoot your code easily. For example, in JavaScript, you can use the following statement to troubleshoot errors')
INSERT [dbo].[Products] ([Id], [Name], [UnitPrice], [Image], [ProductDate], [Available], [CategoryId], [Quantity], [Description]) VALUES (1019, N'Teatime Chocolate Biscuits', 9.2, N'1019.jpg', CAST(0x4A2B0B00 AS Date), 1, 1002, 1, N'EmEditor uses JavaScript or VBScript for its macro language, so those who are familiar with HTML or Windows scripting will be able to write macros with little difficulty. For those unfamiliar with scripting languages, EmEditor can record keystrokes, which can then be saved in a macro file that can easily be loaded in different situations. With the use of JavaScript or VBScript, you can also troubleshoot your code easily. For example, in JavaScript, you can use the following statement to troubleshoot errors')
INSERT [dbo].[Products] ([Id], [Name], [UnitPrice], [Image], [ProductDate], [Available], [CategoryId], [Quantity], [Description]) VALUES (1020, N'Sir Rodney''s Marmalade', 81, N'1020.jpg', CAST(0x342F0B00 AS Date), 1, 1002, 1, N'EmEditor uses JavaScript or VBScript for its macro language, so those who are familiar with HTML or Windows scripting will be able to write macros with little difficulty. For those unfamiliar with scripting languages, EmEditor can record keystrokes, which can then be saved in a macro file that can easily be loaded in different situations. With the use of JavaScript or VBScript, you can also troubleshoot your code easily. For example, in JavaScript, you can use the following statement to troubleshoot errors')
INSERT [dbo].[Products] ([Id], [Name], [UnitPrice], [Image], [ProductDate], [Available], [CategoryId], [Quantity], [Description]) VALUES (1075, N'RhanbrAu Klosterbier', 7.75, N'1075.jpg', CAST(0x880B0B00 AS Date), 1, 1000, 1, N'EmEditor uses JavaScript or VBScript for its macro language, so those who are familiar with HTML or Windows scripting will be able to write macros with little difficulty. For those unfamiliar with scripting languages, EmEditor can record keystrokes, which can then be saved in a macro file that can easily be loaded in different situations. With the use of JavaScript or VBScript, you can also troubleshoot your code easily. For example, in JavaScript, you can use the following statement to troubleshoot errors')
INSERT [dbo].[Products] ([Id], [Name], [UnitPrice], [Image], [ProductDate], [Available], [CategoryId], [Quantity], [Description]) VALUES (1076, N'Lakkalik AAri', 18, N'1076.jpg', CAST(0x0AFA0A00 AS Date), 1, 1000, 1, N'EmEditor uses JavaScript or VBScript for its macro language, so those who are familiar with HTML or Windows scripting will be able to write macros with little difficulty. For those unfamiliar with scripting languages, EmEditor can record keystrokes, which can then be saved in a macro file that can easily be loaded in different situations. With the use of JavaScript or VBScript, you can also troubleshoot your code easily. For example, in JavaScript, you can use the following statement to troubleshoot errors')
INSERT [dbo].[Products] ([Id], [Name], [UnitPrice], [Image], [ProductDate], [Available], [CategoryId], [Quantity], [Description]) VALUES (1077, N'Original Frankfurter grane SoaŸe', 13, N'1077.gif', CAST(0x27020B00 AS Date), 1, 1001, 1, N'EmEditor uses JavaScript or VBScript for its macro language, so those who are familiar with HTML or Windows scripting will be able to write macros with little difficulty. For those unfamiliar with scripting languages, EmEditor can record keystrokes, which can then be saved in a macro file that can easily be loaded in different situations. With the use of JavaScript or VBScript, you can also troubleshoot your code easily. For example, in JavaScript, you can use the following statement to troubleshoot errors')
INSERT [dbo].[Products] ([Id], [Name], [UnitPrice], [Image], [ProductDate], [Available], [CategoryId], [Quantity], [Description]) VALUES (1081, N'Chai', 19, N'1081.jpg', CAST(0x910D0B00 AS Date), 1, 1000, 1, N'EmEditor uses JavaScript or VBScript for its macro language, so those who are familiar with HTML or Windows scripting will be able to write macros with little difficulty. For those unfamiliar with scripting languages, EmEditor can record keystrokes, which can then be saved in a macro file that can easily be loaded in different situations. With the use of JavaScript or VBScript, you can also troubleshoot your code easily. For example, in JavaScript, you can use the following statement to troubleshoot errors')
INSERT [dbo].[Products] ([Id], [Name], [UnitPrice], [Image], [ProductDate], [Available], [CategoryId], [Quantity], [Description]) VALUES (1083, N'Mishi Kobe Niku', 97, N'1083.jpg', CAST(0x21150B00 AS Date), 1, 1005, 1, N'EmEditor uses JavaScript or VBScript for its macro language, so those who are familiar with HTML or Windows scripting will be able to write macros with little difficulty. For those unfamiliar with scripting languages, EmEditor can record keystrokes, which can then be saved in a macro file that can easily be loaded in different situations. With the use of JavaScript or VBScript, you can also troubleshoot your code easily. For example, in JavaScript, you can use the following statement to troubleshoot errors')
INSERT [dbo].[Products] ([Id], [Name], [UnitPrice], [Image], [ProductDate], [Available], [CategoryId], [Quantity], [Description]) VALUES (1084, N'Change-New', 19, N'1084.jpg', CAST(0xE3030B00 AS Date), 1, 1000, 1, N'EmEditor uses JavaScript or VBScript for its macro language, so those who are familiar with HTML or Windows scripting will be able to write macros with little difficulty. For those unfamiliar with scripting languages, EmEditor can record keystrokes, which can then be saved in a macro file that can easily be loaded in different situations. With the use of JavaScript or VBScript, you can also troubleshoot your code easily. For example, in JavaScript, you can use the following statement to troubleshoot errors')
SET IDENTITY_INSERT [dbo].[Products] OFF

• Câu lệnh select trên

Mã:

GO
USE [EShopV103]
GO
select c.NameVN 'Category',p.Name 'Product'
from Products p
   join Categories c on c.Id=p.CategoryId
go
select c.NameVN 'Category',p.Name 'Product'
from Products p
   right join Categories c on c.Id=p.CategoryId