Giới thiệu phép nối – JOIN trong SQL Server

JOIN được sử dụng để truy xuất dữ liệu từ hai hoặc nhiều bảng dựa trên mối quan hệ logic giữa các bảng. Một phép nối (JOIN) thường chỉ định mối quan hệ khóa ngoại giữa các bảng. Nó xác định cách thức mà hai bảng có liên quan với nhau trong một truy vấn bằng cách:

• Chỉ định cột từ mỗi bảng sẽ được sử dụng cho phép nối (JOIN). Một phép nối (JOIN) điển hình chỉ định một khóa ngoại từ một bảng và khóa liên quan của nó trong bảng khác.
• Chỉ định một toán tử logic như =, <> được sử dụng để so sánh các giá trị từ các cột.

JOIN có thể được chỉ định trong mệnh đề FROM hoặc WHERE.

Cú pháp:

SELECT <ColumnName1>, <ColumnName2>, ... <ColumnNameN> FROM Table_Name1 AS alias_1 JOIN Table_Name2 AS alias_2  ON alias_1.<RelatedColumn> = alias_2.<RelatedColumn>

Giả định rằng bạn muốn lấy ra danh sách các tên nhân sự, last name, job title từ bảng HumanResources.Employee và Person.Person. Để lấy thông tin của 2 bảng, cần phải nối 2 bảng lại theo liên kêt khóa ngoại của cột BusinessEntiyID. Ví dụ:

SELECT A.FirstName,A.LastName,B.JobTitle FROM Person.Person A JOIN HumanResources.Employee B ON A.BusinessEntityID = B.BusinessEntityID

Phát triển vấn đề này thêm, chúng ta có 3 kiểu JOINS khái niệm là:

• Inner Joins
• Outer Joins
• Self Joins

Inner Join

Một phép inner join được hình thành khi các bản ghi từ hai bảng chỉ được kết hợp nếu các hàng từ cả hai bảng được so khớp dựa trên một cột chung.

Sự khác biệt giữa JOIN và INNER JOIN: JOIN trả về tất cả các hàng từ các bảng trong đó bản ghi khóa của một bảng bằng các bản ghi khóa của bảng khác . INNER JOIN chọn tất cả các hàng từ cả hai bảng tham gia miễn là có sự trùng khớp giữa các cột

Cú pháp:

SELECT <ColumnName1>,<ColumnName2> ... <ColumnNameN> FROM Table_A AS Table_Alias_A INNER JOIN Table_B AS Table_Alias_B ON Table_Alias_A.<RelatedColumn> = Table_Alias_B.<RelatedColumn>

Ví dụ:

SELECT A.FirstName, A.LastName, B.JobTitle FROM Person.Person A INNER JOIN HumanResources.Employee B ON A.BusinessEntityID = b.BusinessEntityID

Outer Join

Phép Outer Join là các câu lệnh nối trả về tất cả các hàng từ ít nhất một trong các bảng được chỉ định trong mệnh đề FROM, miễn là các hàng đó đáp ứng bất kỳ điều kiện WHERE hoặc HAVING nào của câu lệnh SELECT. Hai loại outer join thường được sử dụng như sau:

• Left Outer Join
• Right Outer Join

Left Outer Join

Kết nối bên ngoài bên trái trả về tất cả các bản ghi từ bảng bên trái và chỉ bản ghi được khớp bên phải.

Cú pháp:

SELECT <ColumnList> FROM Table_A AS ALIAS_A LEFT OUTER JOIN Table_B AS ALIAS_B ON ALIAS_A.<RelatedColumn> = ALIAS_B.<RelatedColumn>

Giả sử bạn muốn lấy về toàn bộ customer id từ bảng Sales.Customers và thông tin order như là ship dates., due dates, lấy ra cả customer không đặt bất khì order nào, nhưng số lượng bản ghi rất lớn nên chúng ta sẽ giới hạn đơn hàng đặt trước 2019, để làm điều này, bạn thực hiện phép LEFT OUTER JOIN như sau:

SELECT A.CustomerID, B.DueDate, B.ShipDAte FROM Sales.Customer A LEFT OUTER JOIN Sales.SalesOrderHeader B ON A.CustomerID = B.CustomerID AND YEAR(B.DueDate) < 2019

Ở đoạn code phía trên, left outer join là một cấu trúc giữa bảng Sales.Customer và Sales.SalesOrderHeader. Bảng được nối dựa trên cột customer id. Trong tình huống này, tất cả bản ghi của bảng bên trái là Sales.Customer và chỉ những bản ghi được khớp của bảng bên phải là Sales.SalesOrderHeader được lấy ra, nếu dữ liệu của bảng bên phải không khớp, bảng bên trái vẫn được lấy ra còn bảng bên phải dữ liệu sẽ trả về null.

Right Outer Join

Right outer join lấy ra toàn bộ bản ghi từ bảng bên phải của phép nối (join), bất kể có dữ liệu khớp trong bảng đầu tiên hay không

SELECT <ColumnList> FROM Left_Table_Name AS Alias_A RIGHT OUTER JOIN Table_B as ALIAS_B ON Alias_A.<RelatedColumn> = Allias_B.<RelatedColumn>

Ví dụ bạn muốn lấy ra tất cả tên sản phẩm từ bảng product và tất cả các đơn hàng tương ứng từ bảng SalesOrderDetail mặc dù có thể tồn tại những bản ghi sản phẩm không khớp với dữ liệu ở bảng SalesOrderDetail ( sản phẩm chưa được bán bao giờ) như sau:

SELECT P.Name,S.SalesOrderID FROM Sales.SalesOrderDetail S RIGHT OUTER JOIN Production.Product P ON P.ProductID = S.ProductID

Self Join

Một phép nối một bảng với chính nó (self-join) sử dụng để thể hiện sự liên quan giữa các bản ghi trên cùng một bảng. Một bảng được nối với chính nó gọi là Self-Join.

Ví dụ, bạn muốn sử dụng self-join để lấy ra các chi tiết của sản phẩm mà có cùng màu sắc trong bảng Production.Product

SELECT p1.ProductID, p1.Color, p1.Name, p2.Name FROM Production.Product p1 INNER JOIN Production.Product p2 ON p1.Color = p2.Color ORDER BY p1.ProductID

Câu lệnh MERGE

Câu lệnh MERGE cho phép bạn duy tmột bảng đích theo các điều kiện nối nhất định trên một bảng nguồn bằng cách sử dụng một câu lệnh duy nhất.

Ví dụ, nếu bạn muốn:

• So sánh lastname và firstname của khách hàng từ 2 bảng nguồn và đích
• Cập nhật thông tin khách hàng ở bảng đích nếu first name và last name khớp
• Thêm bản ghi mới trong bảng đích nếu last name và first names trong bảng nguồn không tồn tại ở bảng đích
• Xóa các bản ghi ở bảng đích nếu last name và first name không khớp với bảng nguồn

Câu lệnh MERGE hoàn thành các nhiệm vụ trong một câu lệnh duy nhất. MERGE cũng cho phép bạn hiển thị những bản ghi đã được chèn, cập nhật hoặc xóa bằng cách sử dụng mệnh đề OUTPUT.

Cú pháp:

MERGE target_table USING source_table ON match_condition WHEN MATCHED THEN UPDATE SET col1 = vale [,Col2 = val2] WHEN [TARGET] NOT MATCHED THEN INSERT (Col1[,Col2...]) VALUES(Val1[,Val2...])
WHEN NOT MATCH BY SOURCE THEN DELETE [OUTPUT $action, inserted.Col1,Deleted.Col1,....];

Giải thích:

• target_table: bảng đích, nơi dữ liệu sẽ thay đổi ở đây
• source_table: bảng nguồn, chứa những bản ghi có thể được thêm, cập nhật, xóa ở bảng đích
• match_conditions: điều kiện phép nối (JOIN) và bất kì toán tử so sánh nào.
• MATCHED: trả về true nếu bản ghi target_table và source_table khớp match_condition.
• NOT MATCHED: trả về true nếu bản ghi từ source_table không tồn tại ở target_table.
• SOURCE NOT MATCH: trả về truy nếu bản ghi tồn tại ở target_table mà không tồn tại ở source_table.
• OUTPUT: mệnh đề tùy chọn cho phép xem những bản ghi đã được theme/xóa/cập nhật ở target_Table.

Câu lệnh MERGE kết thúc bằng dấu chấm phẩy (;).

Ví dụ:

use AdventureWorks2019
go
SET IDENTITY_INSERT [Person].[AddressType] ON MERGE INTO [Person].[AddressType] AS Target 
USING (VALUES (1,'Billing') , (2,'Home'),(3,'Headquarters'),(4,'Primary'),(5,'Shipping'),(6,'Archival'),(7,'Contact'),(8,'Alternative')) AS Source ([AddressTypeID],[Name]) 
ON (Target.[AddressTypeID] = Source.[AddressTypeID]) 
WHEN MATCHED AND (Target.[Name] <> Source.[Name]) THEN UPDATE SET [Name] = Source.[NAME]
WHEN NOT MATCHED BY TARGET THEN INSERT ([AddressTypeID],[Name]) VALUES(Source.[AddressTypeID],Source.[Name])
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT $action, Inserted.[AddressTypeID], Inserted.Name, Deleted.[AddressTypeID], Deleted.Name;

Bảng Person.AddressType là bảng đích, dữ liệu mẫu insert vào thông qua lệnh USING (VALUES (1,’Billing’) , (2,’Home’),(3,’Headquarters’),(4,’Primary’),(5,’Shipping’),(6,’Archival’),(7,’Contact’),(8,’Alternative’)) AS Source là bảng nguồn, điều kiện so khớp là cột AddressTypeID của cả 2 bảng source và target. Nếu điều kiện so khớp được đánh giá là false (NOT MATCHED). các bản ghi mới sẽ được thêm vào bảng đích. Nếu các điều kiện so khớ trả ra true (MATCHED), bản ghi sẽ được cập nhật vào bảng đích dựa theo dữ liệu của bảng nguồn.

Nếu bản ghi trong bảng đích không khớp với bảng nguồn (NOT MATCHED BY SOURCE), chúng sẽ được xóa khỏi bảng đích. Câu lệnh cuối cùng vai trò là báo cáo những hàng đã được thêm/cập nhật/xóa và hiển thị ra output.