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

Thảo luận trong 'Kiến thức lập trình SQL & MySQL' bắt đầu bởi phamcongson, 12/3/17.

  1. phamcongson

    phamcongson

    Police

    Tham gia ngày:
    3/6/13
    Bài viết:
    511
    Đã được thích:
    93
    Điểm thành tích:
    28
    Giới tính:
    Nam
    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
    
    left-join-va-right-join-trong-sql-server.png
     
    Cảm ơn đã xem bài:

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



Like và Share ủng hộ ITSEOVN