JOIN trong SQL (Phép nối) là một trong những kỹ thuật biến đổi dữ liệu quan trọng trong phân tích dữ liệu. Việc hiểu rõ cách thức phép nối được thực thi trong SQL sẽ giúp các nhà phân tích tránh được các rủi ro không cần thiết trong quá trình làm việc với dữ liệu. Bài viết này sẽ giúp bạn nắm được các kiến thức cơ bản về phép nối.

Giới thiệu về JOIN và các cú pháp trong SQL

Giới thiệu về JOIN

JOIN là phép kết nối dữ liệu từ nhiều bảng lại với nhau. JOIN cho phép truy vấn các cột dữ liệu từ nhiều bảng khác nhau để trả về trong cùng một tập kết quả. JOIN giúp tái hiện lại thông tin thế giới thực từ dữ liệu lưu trữ trong mô hình quan hệ. Ví dụ, bạn cần JOIN bảng BanHang với bảng SanPham thông qua SanPhamID để lấy về thông tin đầy đủ của một đơn hàng bao gồm cả tên sản phẩm, vì người dùng cần quan tâm đến sản phẩm đó là gì thay vì mã hiệu của nó.

Các loại JOIN phổ biến

Theo tiêu chuẩn ISO, SQL cung cấp các kiểu JOIN là INNER JOIN, OUTER JOIN, và CROSS JOIN.

Trong đó:

INNER JOIN trả về kết quả là các bản ghi mà trường được join ở hai bảng khớp nhau, các bản ghi chỉ xuất hiện ở một trong hai bảng sẽ bị loại.

Hình 1. Mô tả Inner Join

Nguồn: Tác giả tự minh họa

OUTER JOIN nới lỏng hơn, lấy về các bản ghi có mặt trong cả hai bảng và cả các bản ghi chỉ xuất hiện ở một trong hai bảng. Kiểu JOIN này được chia làm hai loại:

FULL OUTER JOIN: kết quả gồm tất cả các bản ghi của cả hai bảng. Với các bản ghi chỉ xuất hiện trong một bảng thì các cột dữ liệu từ bảng kia được điền giá trị NULL.

Hình 3. Mô tả Full Outer Join

Nguồn: Tác giả tự minh họa

HALF OUTER JOIN (LEFT hoặc RIGHT): nếu bảng A LEFT OUTER JOIN với bảng B thì kết quả gồm các bản ghi có trong bảng A, với các bản ghi không có mặt trong bảng B thì các cột từ B được điền NULL. Các bản ghi chỉ có trong B mà không có trong A sẽ không được trả về.

Hình 4. Mô tả Half Outer Join

Nguồn: Tác giả tự minh họa

Bảng được xác định là LEFT trong phép JOIN là bảng được viết trước.

CROSS JOIN: mỗi bản ghi của bảng A được kết hợp với tất cả các bản ghi của bảng B, tạo thành một tích Đề-các giữa hai bảng (số bản ghi trả về bằng tích của số bản ghi trong hai bảng).

Ví dụ:

• Khởi tạo dữ liệu kiểm thử trên SQL Server 2019 với câu lệnh dưới đây:

CREATE TABLE #T1(ID1 INT, Ten VARCHAR(100), NamSinh INT )
INSERT INTO #T1
SELECT 1, 'Mozart', 1756 UNION ALL
SELECT 2, 'Beethoven', 1770 UNION ALL
SELECT 3, 'Chopin', 1810
 
CREATE TABLE #T2(ID2 INT, Email VARCHAR(100) )
INSERT INTO #T2
SELECT 2, 'beethoven@gmail.com' UNION ALL
SELECT 3, 'chopin@hotmail.com'  UNION ALL
SELECT 4, 'haydn@yahoo.com'  UNION ALL
SELECT 5, 'bach@yahoo.com'

INNER JOIN:

SELECT *
FROM #T1 JOIN #T2 on #T1.ID1 = #T2.ID2

Bảng 1. Kết quả Inner Join

FULL OUTER JOIN:

SELECT *
FROM #T1 FULL OUTER JOIN #T2 on #T1.ID1 = #T2.ID2

Bảng 2. Kết quả Outer Join

LEFT OUTER JOIN:

SELECT *
FROM #T1 LEFT JOIN #T2 on #T1.ID1 = #T2.ID2

Bảng 3. Kết quả Left Outer Join

CROSS JOIN:

SELECT *
FROM #T1 CROSS JOIN #T2

Bảng 4. Kết quả Cross Join

Một số lưu ý với các loại JOIN:

• Cú pháp rút gọn: “A INNER JOIN B” có thể viết tắt thành “A JOIN B”, còn “A LEFT OUTER JOIN B” có thể viết “A LEFT JOIN B”
• Tính đối xứng: Trong các loại JOIN trên, chỉ trừ HALF OUTER JOIN còn tất cả đều có tính đối xứng, nghĩa là “A JOIN B” tương tự như “B JOIN A”. Riêng HALF OUTER JOIN thì phân biệt thứ tự, ví dụ “A LEFT JOIN B” khác với “B LEFT JOIN A”. Tuy nhiên, “A LEFT JOIN B” tương đương với “B RIGHT JOIN A”.
• Sự bùng nổ bản ghi trong CROSS JOIN: Số bản ghi kết quả của CROSS JOIN là tích số bảng ghi 2 bảng thành phần, nếu hai bảng có số bản ghi tương ứng là 1 nghìn và 1 triệu thì kết quả sẽ là 1 tỷ bản ghi. CROSS JOIN không được sử dụng thường xuyên trong thực tế, một số ứng dụng có thể kể đến như sinh ra các cách kết hợp khả thi có thể của các yếu tố đầu vào, ví dụ: Mã sinh viên và Môn học vì tất cả các sinh viên đều phải tham gia đủ các môn học.

Một số lưu ý khi làm việc với JOIN trong SQL

Các dạng cú pháp JOIN

Các CSDL hỗ trợ 2 dạng cú pháp phổ biến:

Dạng ANSI – ISO:

--INNER JOIN kiểu ANSI
SELECT *
FROM #T1 JOIN #T2 ON #T1.ID = #T2.ID

-- LEFT OUTER JOIN kiểu ANSI
SELECT *
FROM #T1 LEFT JOIN #T2 ON #T1.ID = #T2.ID

Một dạng khác:

-- INNER JOIN kiểu cũ
SELECT *
FROM #T1, #T2
WHERE #T1.ID = #T2.ID

-- LEFT OUTER JOIN kiểu cũ
SELECT *
FROM #T1, #T2
WHERE #T1.ID *= #T2.ID

Hai cách viết này không khác biệt về cách thức thực thi, hiệu năng và kết quả. Tuy nhiên, cách viết theo dạng ANSI – ISO phổ biến và dễ kiểm soát hơn. Kiểu viết này gần với diễn đạt của ngôn ngữ tự nhiên hơn, nó tách bạch rõ ràng điều kiện join ra khỏi điều kiện lọc dữ liệu (dùng ở mệnh đề WHERE). Một số hệ Quản trị CSLD mới đã không còn hỗ trợ viết JOIN trong mệnh đề WHERE.

Kết luận: Phép JOIN viết theo kiểu ANSI là cách nên được sử dụng.

LEFT JOIN khi kết hợp với mệnh đề WHERE

Việc thực hiện phép JOIN kết hợp với mệnh đề WHERE cũng rất phổ biến trong tiến trình phân tích dữ liệu. Điều này không có rủi ro gì khi làm việc với INNER JOIN, tuy nhiên, có thể mang đến những kết quả khác với kỳ vọng khi làm việc với LEFT JOIN.

Chúng ta cùng xem xét ví dụ sau đây:

Tạo một Database về bán hàng điện thoại, gồm hai bảng là MatHang bao gồm các loại điện thoại và BanHang chứa các giao dịch bán hàng.

-- Tạo bảng MatHang
CREATE TABLE #MatHang(ID INT, Ten VARCHAR(100))
INSERT INTO #MatHang VALUES(1,'iPhone 4')
INSERT INTO #MatHang VALUES(2,'iPhone 4S')
INSERT INTO #MatHang VALUES(3,'iPhone 5')
INSERT INTO #MatHang VALUES(4,'iPhone 5S')
 
-- Tạo bảng BanHang
CREATE TABLE #BanHang(ID INT IDENTITY, MatHangID INT, Ngay DATETIME)
INSERT INTO #BanHang VALUES(4,'2021-02-25')
INSERT INTO #BanHang VALUES(2,'2021-02-26')
INSERT INTO #BanHang VALUES(2,'2021-02-26')
INSERT INTO #BanHang VALUES(3,'2021-02-26')
INSERT INTO #BanHang VALUES(2,'2021-02-26')

Trường hợp 1: Liệt kê tất cả các loại điện thoại bán được trong ngày 26 tháng 2 năm 2021. Chúng ta có câu lệnh sau:

SELECT MH.ID
, MH.Ten
, BH.Ngay
FROM #MatHang MH
JOIN #BanHang BH ON MH.ID = BH.MatHangID
WHERE BH.Ngay = '2021-02-26'

Bảng 5. Kết quả LEFT JOIN khi kết hợp với mệnh đề WHERE (Trường hợp 1)

Trường hợp 2: Liệt kê tất cả các điện thoại kèm theo thông tin bán hàng nếu có trong ngày 26 tháng 2 năm 2021. Vì yêu cầu tìm tất cả các điện thoại hiện có trong Database nên chúng ta cần dùng LEFT JOIN. Cú pháp được điều chỉnh như sau:

SELECT MH.ID
, MH.Ten
, BH.Ngay
FROM #MatHang MH
LEFT JOIN #BanHang BH ON MH.ID = BH.MatHangID
WHERE BH.Ngay = '2021-02-26'

Bảng 6. Kết quả LEFT JOIN khi kết hợp với mệnh đề WHERE (Trường hợp 2)

Nhận thấy, kết quả không có khác biệt so với sử dụng INNER JOIN và không đáp ứng được yêu cầu đề bài. Theo đúng yêu cầu, tất cả các sản phẩm phải có mặt trong kết quả nhưng iphone 4 và iphone 5S bị loại ra. Sau khi thực hiện LEFT JOIN, tập trung gian có đầy đủ tất cả bản ghi từ 2 bảng. Trong tập trung gian, các sản phẩm chưa được bán sẽ có ngày NULL hoặc bằng các giá trị ngày bán khác (nếu chỉ có bán khác ngày 2021-02-26). Vì vậy, sau khi áp dụng mệnh đề WHERE, chỉ các bản ghi có Ngày = ‘2021-02-26’ được giữ lại.

Để làm rõ hơn: ON và WHERE là hai tiến trình độc lập, các điều kiện ON thực hiện trước rồi mới đến các điều kiện ở WHERE.

• Mệnh đề ON có 2 công việc:
  • Liên kết giữa bảng trái và phải để xác định số dòng trả về tại thời điểm này.
  • Xác định giá trị các trường của bảng phải trong kết quả trả về tại thời điểm này.
• Mệnh đề ON không thực hiện lọc kết quả, nếu không có mệnh đề WHERE, kết quả luôn có số dòng lớn hơn hoặc bằng bảng trái.
• Mệnh đề WHERE có nhiệm vụ lọc kết quả sau khi thực hiện bởi ON.

Phương án thực thi: Chúng ta chuyển điều kiện Ngay = ‘2021-02-26’ từ mệnh đề WHERE sang mệnh đề ON.

SELECT MH.ID
, MH.Ten
, BH.Ngay--, BH.Gia
FROM #MatHang MH
LEFT JOIN #BanHang BH ON MH.ID = BH.MatHangID AND BH.Ngay = '2021-02-26'

Bảng 7. Kết quả

Kết luận:

• Khi thực hiện phép JOIN nên lấy bảng Trái là bảng trung tâm, sau đó LEFT JOIN sang các bảng khác.
• Khi thực hiện LEFT JOIN, mệnh đề WHERE chỉ nên chứa điều kiện so sánh ở bảng Trái, điều kiện so sánh ở các bảng khác nên để ở mệnh đề ON. Với RIGHT JOIN làm ngược lại.

JOIN bất cân bằng

Thông thường khi viết lệnh JOIN, trong biểu thức ở mệnh đề ON, chúng ta dùng toán tử “=”. Kiểu JOIN này tạm gọi là JOIN cân bằng (EQui JOIN), còn kiểu JOIN là bất cân bằng (Non-EQui) là khi biểu thức ở mệnh đề join dùng các toán tử bất cân bằng như >, <, !=…

Chúng ta cùng xem xét ví dụ sau:

Khởi tạo bảng dữ liệu:

CREATE TABLE #T1(ID1 INT, Ten VARCHAR(100), NamSinh INT, SoLuongTP INT)
INSERT INTO #T1
SELECT 1, 'Mozart', 1756, 626 UNION ALL
SELECT 2, 'Beethoven', 1770, 722  UNION ALL
SELECT 3, 'Chopin', 1810, 265
 
CREATE TABLE #T2(ID2 INT, Email VARCHAR(100) )
INSERT INTO #T2
SELECT 2, 'beethoven@gmail.com' UNION ALL
SELECT 3, 'chopin@hotmail.com'  UNION ALL
SELECT 4, 'haydn@yahoo.com'  UNION ALL
SELECT 5, 'bach@yahoo.com'

Chúng ta chạy thử câu lệnh:

SELECT *
FROM #T1 JOIN #T2 on #T1.ID1 < #T2.ID2

Bảng 8. Kết quả

Nhận thấy: Kết quả trả về đã chạy đúng theo mệnh đề ON, ID2 > ID1. Trong trường hợp INNER JOIN, kết quả chúng ta nhận được sẽ hình thành giống như chúng ta thực hiện CROSSJOIN T1 và T2 sau đó áp dụng điều kiện lọc mệnh đề ON lên kết quả đang có.

Ứng dụng của JOIN bất cân bằng

JOIN bất cân bằng trong tính lũy kế.

Trường hợp chúng ta muốn một bảng kết quả có tên tác giả và tổng số tác phẩm của các tác giả trước đó (tính theo năm sinh nhỏ hơn), chúng ta có thể thực hiện truy vấn như sau:

SELECT Artist.ID1
, Artist.Ten
, Artist.NamSinh
, Artist.SoLuongTP
, SUM(Earlier.SoLuongTP) as Total_Earlier
FROM #T1 as Artist  
LEFT JOIN #T1 as Earlier on Earlier.NamSinh <=  Artist.NamSinh
GROUP BY Artist.ID1
, Artist.Ten
, Artist.NamSinh
, Artist.SoLuongTP

Bảng 9. Kết quả

Một bảng dữ liệu trong trường hợp này có thể đóng cả vai trò bảng Trái và Phải trong phép JOIN, chúng ta phải gán Alias khác nhau cho hai vai trò này.

JOIN bất cân bằng trong xử lý các thay đổi theo thời gian.

Cùng xem xét bộ dữ liệu gồm 2 bảng:

Danh sách sản phẩm: SanPham

Bảng 10. Danh sách sản phẩm

Bảng sản phẩm có Giá Vốn (GiaVon) thay đổi theo thời gian, vì vậy, mỗi dòng của bảng sẽ gồm Mã Sản Phẩm (MaSP), Giá Vốn (GiaVo), Ngày áp dụng giá vốn (NgayBatDau) này và Ngày kết thúc (NgayKetThuc) áp dụng. Đây là kỹ thuật xử lý thường thấy cho các bảng dữ liệu thay đổi chậm theo thời gian (Slowly Changing Dimension).

Để tránh các rủi ro liên quan đến NULL, người thiết kế quy ước ngày bắt đầu chưa xác định là ngày ‘1900-01-01’ và ngày kết thúc chưa xác định là ‘2099-01-01’.

Danh sách hàng bán: BanHang

Bảng 11. Danh sách hàng bán

Với yêu cầu lấy các trường dữ liệu: Mã Sản Phẩm, Giá Bán, Ngày Bán, Giá Vốn

Chúng ta có truy vấn sau:

SELECT BanHang.MaSP
, BanHang.GiaBan
, BanHang.NgayBan
, SanPham.GiaVon
FROM BanHang 
LEFT JOIN SanPham on SanPham.MASP = BanHang.MaSP 
                  and SanPham.NgayBatDau <= BanHang.NgayBan
                  and SanPham.NgayKetThuc => BanHang.NgayKetThuc

Bảng 12. Kết Quả

Kết luận: Phép JOIN bất cân bằng có thể áp dụng trong các trường hợp cần lựa chọn bản ghi phù hợp với nhiều điều kiện lọc khác nhau. Bài toán này có thể mở rộng thêm với nhiều tình huống sử dụng khác.

Đọc thêm về JOIN tại đây

TÀI LIỆU THAM KHẢO

Microsoft (2022), SQL Server technical documentation, [https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15, accessed 18 May 2022].

Melton (2016), “ISO/IEC 9075-1 Information technology-Database languages-SQL-Part 1: Framework (SQL/Framework)”, ISO/IEC, 2016(E), 9075-1.