fbpx
  • Hoang’s Blog
  • Career Stories
No Result
View All Result
  • Hoang’s Blog
  • Career Stories
No Result
View All Result
No Result
View All Result

Câu lệnh JOIN trong SQL và các lưu ý khi Phân tích dữ liệu

August 1, 2023
in SQL, Tutorials
Share on FacebookShare on LinkedinShare on Email

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

  • 1 Giới thiệu về JOIN và các cú pháp trong SQL
    • 1.1 Giới thiệu về JOIN
    • 1.2 Các loại JOIN phổ biến
  • 2 Một số lưu ý khi làm việc với JOIN trong SQL
    • 2.1 Các dạng cú pháp JOIN
    • 2.2 LEFT JOIN khi kết hợp với mệnh đề WHERE
    • 2.3 JOIN bất cân bằng
      • 2.3.1 Ứng dụng của JOIN bất cân bằng

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

Inner Join trong SQL

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

Full outer Join trong SQL

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

Outer Join trong SQL

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

ID1TenID2Email
2Beethoven2beethoven@gmail.com
3Chopin3chopin@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

ID1TenID2Email
1MozartNULLNULL
2Beethoven2beethoven@gmail.com
3Chopin3chopin@hotmail.com
NULLNULL4haydn@yahoo.com
NULLNULL5bach@yahoo.com

LEFT OUTER JOIN:

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

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

ID1TenID2Email
1MozartNULLNULL
2Beethoven2beethoven@gmail.com
3Chopin3chopin@hotmail.com

CROSS JOIN:

SELECT *
FROM #T1 CROSS JOIN #T2

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

ID1TenID2Email
1Mozart2beethoven@gmail.com
1Mozart3chopin@hotmail.com
1Mozart4haydn@yahoo.com
1Mozart5bach@yahoo.com
2Beethoven2beethoven@gmail.com
2Beethoven3chopin@hotmail.com
2Beethoven4haydn@yahoo.com
2Beethoven5bach@yahoo.com
3Chopin2beethoven@gmail.com
3Chopin3chopin@hotmail.com
3Chopin4haydn@yahoo.com
3Chopin5bach@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.

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)

IDTenNgay
2iPhone 4S2021-02-26
2iPhone 4S2021-02-26
2iPhone 4S2021-02-26
3iPhone 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)

IDTenNgay
2iPhone 4S2021-02-26
2iPhone 4S2021-02-26
2iPhone 4S2021-02-26
3iPhone 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 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ả

IDTenNgay
1iPhone 4NULL
2iPhone 4S2021-02-26
2iPhone 4S2021-02-26
2iPhone 4S2021-02-26
3iPhone 52021-02-26
4iPhone 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:

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ả

ID1TenNamSinhSoLuongTPID2Email
1Mozart17566262beethoven@gmail.com
1Mozart17566263chopin@hotmail.com
1Mozart17566264haydn@yahoo.com
1Mozart17566265bach@yahoo.com
2Beethoven17707223chopin@hotmail.com
2Beethoven17707224haydn@yahoo.com
2Beethoven17707225bach@yahoo.com
3Chopin18102654haydn@yahoo.com
3Chopin18102655bach@yahoo.com

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ả

ID1TenNamSinhSoLuongTPTotal_Earlier
1Mozart1756626626
2Beethoven17707221348
3Chopin18102651613

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

MaSPGiaVonNgayBatDauNgayKetThuc
191900-01-012022-05-20
1102022-05-212999-01-01
2121900-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

MaSPGiaBanNgayBan
1102022-05-18
2112022-05-19
1122022-05-20
2132022-05-21
1122022-05-22
1122022-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 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ả

MaSPDoanhSoNgayBanGiaVon
1102022-05-189
2112022-05-1912
1122022-05-209
2132022-05-2112
1122022-05-2210
1122022-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.

Tags: #join#sql#tutorials
ShareShareSend
Previous Post

LƯU TRỮ DỮ LIỆU PHI QUAN HỆ VỚI AZURE STORAGE

Next Post

Nên đọc trước khi bước chân vào ngành Data – Phần 1: Kinh nghiệm ứng tuyển

Tô Mạnh Hoàng

Tô Mạnh Hoàng

Related Posts

Data

Microsoft Fabric là gì? Dành cho ai và tại sao nên sử dụng?

July 27, 2023

Microsoft Fabric (hay Fabric) là một giải pháp phân tích tổng hợp dành cho doanh nghiệp.

SQL

Temporal table: Lưu trữ và truy vấn dữ liệu quá khứ dễ dàng

May 30, 2023

Temporal table là một loại bảng trong SQL Server có tính năng hỗ trợ lưu trữ dữ liệu tại nhiều khoảng thời gian trong cơ sở dữ liệu.

Data

Dynamic Row-level Security: Tổ chức dữ liệu và phân quyền trong Power BI

May 30, 2023

Phân quyền trong Power BI cho người dùng là một phần quan trọng trong cả quy trình ứng dụng dữ liệu trong doanh nghiệp. Bởi nó ảnh hưởng đến bảo mật dữ liệu và giải quyết các vấn đề...

Azure

LƯU TRỮ DỮ LIỆU PHI QUAN HỆ VỚI AZURE STORAGE

May 11, 2023

Lưu trữ dữ liệu phi quan hệ như video, ảnh, văn bản tự do, thông tin mã hóa,...bằng kho Azure Table Storage và Azure Blob Storage

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

  • Azure (1)
  • Chuyện nghề (2)
  • Data (7)
  • Learn (1)
  • Other tools (1)
  • Power BI (21)
  • Skills & Knowledge (29)
  • SQL (9)
  • Technique (31)
  • Tutorials (38)

Most Popular

  • MÔ HÌNH HÓA DỮ LIỆU: STAR SCHEMA, BẢNG DIM VÀ BẢNG FACT

    0 shares
    Share 0 Tweet 0
  • Câu lệnh JOIN trong SQL và các lưu ý khi Phân tích dữ liệu

    0 shares
    Share 0 Tweet 0
  • TÌM HIỂU VỀ IDENTITY VÀ SEQUENCE TRONG SQL 

    0 shares
    Share 0 Tweet 0
  • CÁCH TẠO BẢNG DATE TRONG POWER BI

    0 shares
    Share 0 Tweet 0
  • CÁCH TỰ ĐỘNG CẬP NHẬT DỮ LIỆU (SCHEDULED REFRESH) TRONG POWER BI SERVICE 

    0 shares
    Share 0 Tweet 0
  • About me
  • Privacy Policy
hoangtomanh@gmail.com

© 2022 Hoang 's blog

No Result
View All Result
  • Hoang’s Blog
  • Career Stories