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.

Các nội dung chính

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, '[email protected]' UNION ALL SELECT 3, '[email protected]' UNION ALL SELECT 4, '[email protected]' UNION ALL SELECT 5, '[email protected]'

INNER JOIN:

SELECT * FROM

T1 JOIN

T2 on

T1.ID1 =

T2.ID2

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

[email protected]@hotmail.com

FULL OUTER JOIN:

SELECT * FROM

T1 FULL OUTER JOIN

T2 on

T1.ID1 =

T2.ID2

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

ID1TenID2Email1MozartNULLNULL2Beethoven2beethoven@[email protected]@[email protected]

LEFT OUTER JOIN:

SELECT * FROM

T1 LEFT JOIN

T2 on

T1.ID1 =

T2.ID2

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

ID1TenID2Email1MozartNULLNULL2Beethoven2beethoven@[email protected]

CROSS JOIN:

SELECT * FROM

T1 CROSS JOIN

T2

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

[email protected]@[email protected]@[email protected]@[email protected]@[email protected]@[email protected]@yahoo.com

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.

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)

IDTenNgay2iPhone 4S2021-02-262iPhone 4S2021-02-262iPhone 4S2021-02-263iPhone 52021-02-26

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)

IDTenNgay2iPhone 4S2021-02-262iPhone 4S2021-02-262iPhone 4S2021-02-263iPhone 52021-02-26

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 * FROM

T1 JOIN

T2 on

T1.ID1 =

T2.ID2

0

Bảng 7. Kết quả

IDTenNgay1iPhone 4NULL2iPhone 4S2021-02-262iPhone 4S2021-02-262iPhone 4S2021-02-263iPhone 52021-02-264iPhone 5SNULL

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:

SELECT * FROM

T1 JOIN

T2 on

T1.ID1 =

T2.ID2

1

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

SELECT * FROM

T1 JOIN

T2 on

T1.ID1 =

T2.ID2

2

Bảng 8. Kết quả

ID1TenNamSinhSoLuongTPID2Email1Mozart17566262beethoven@[email protected]@[email protected]@hotmail.com2Beethoven17707224haydn@[email protected]@[email protected]

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 * FROM

T1 JOIN

T2 on

T1.ID1 =

T2.ID2

3

Bảng 9. Kết quả

ID1TenNamSinhSoLuongTPTotal_Earlier1Mozart17566266262Beethoven177072213483Chopin18102651613

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

MaSPGiaVonNgayBatDauNgayKetThuc191900-01-012022-05-201102022-05-212999-01-012121900-01-012999-01-01

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

MaSPGiaBanNgayBan1102022-05-182112022-05-191122022-05-202132022-05-211122022-05-221122022-05-23

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 * FROM

T1 JOIN

T2 on

T1.ID1 =

T2.ID2

4

Bảng 12. Kết Quả

MaSPDoanhSoNgayBanGiaVon1102022-05-1892112022-05-19121122022-05-2092132022-05-21121122022-05-22101122022-05-2310

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.