SQL là một ngôn ngữ mạnh mẽ để thao tác dữ liệu, đặc biệt khi kết hợp dữ liệu từ các bảng khác nhau. Hôm nay, chúng ta sẽ cùng DATAPOT khám phá hai cách phổ biến để hợp nhất các bảng trong SQL. Nếu bạn đã bỏ lỡ các bài viết giá trị trong phần trước, hãy cùng xem lại tại đây.
Kết hợp bảng sử dụng JOIN
Mệnh đề JOIN trong SQL được sử dụng để kết nối dữ liệu từ hai hay 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 thường được sử dụng để hợp nhất dữ liệu trong cơ sở dữ liệu có quan hệ (Relational Database).
Khóa chính và khóa ngoại
Khóa chính (Primary Key - PK)
Khóa chính được sử dụng để định danh từng dòng dữ liệu trong bảng.
Đặc điểm:
- Khóa chính chứa giá trị không trùng lặp.
- Mỗi bảng chỉ có một khóa chính duy nhất (Khóa chính có thể là một trường hoặc tổ hợp các trường).
- Khóa chính không được chứa NULL.
Khóa ngoại (Foreign Key - FK)
Khóa ngoại chứa giá trị tham chiếu dùng để liên kết với khóa chính của bảng khác.
Đặc điểm:
- Khóa ngoại có thể là một trường hoặc tổ hợp trường.
- Một bảng dữ liệu có thể có nhiều khóa ngoại trong 1 bảng.
- Khóa ngoại có thể chứa giá trị NULL.
Ví dụ: Nhìn vào lược đồ (Schema) SaleLT:
- Bảng Customer có PK là CustomerID.
- SalesOrderDetail có PK là SalesOrderDetailID.
- SalesOrderHeader có PK là SalesOrderID.
- SalesOrderDetail có FK là ProductID và SalesOrderID.
- SalesOrderHeader có FK là AddressID và CustomerID.
Các loại JOIN
Kết hợp điểm chung (Inner join)
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.
Cú pháp của INNER JOIN:
SELECT *
FROM Customer AS CST
INNER JOIN CustomerAddress AS CTA
ON CST.CustomerID = CTA.CustomerID;
Kết hợp trái (Left join)
Nếu bảng A LEFT 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ề.
Bảng được xác định là left trong phép JOIN là bảng được viết trước.
Cú pháp của LEFT JOIN:
SELECT *
FROM Customer AS CST
LEFT JOIN CustomerAddress AS CTA
ON CST.CustomerID = CTA.CustomerID;
Kết hợp phải (Right join)
Nếu bảng được kết hợp phải với bảng B thì kết quả gồm các bản ghi có trong bảng B. Với các bản ghi không có mặt trong bảng A thì các cột từ bảng A được trả về NULL. Các bản ghi chỉ có trong bảng A mà không có trong bảng B sẽ không được trả về.
Cú pháp của RIGHT JOIN:
SELECT *
FROM Customer AS CST
RIGHT JOIN CustomerAddress AS CTA
ON CST.CustomerID = CTA.CustomerID;
Kết hợp chéo (Cross join)
Kết hợp chéo (Cross join) là kết hợp giữa các hàng của hai bảng với nhau, mỗi hàng trong bảng thứ nhất sẽ kết hợp với N hàng của bảng thứ hai. Kết quả của kết hợp chéo (Cross join) sẽ có số hàng bằng tích số của hai bảng.
Do kết quả trả ra của Cross join có thể rất lớn là tích số của hai bảng, cần cân nhắc sự cần thiết khi sử dụng kết hợp chéo (Cross join).
Cú pháp của CROSS JOIN:
SELECT *
FROM Customer AS CST
CROSS JOIN CustomerAddress AS CTA;
Kết hợp tất cả (Outer join/Full Outer Join)
Kết hợp chung (Outer join/Full Outer Join/FULL Join) là kết hợp tất cả các hàng với nhau.
Nếu không có sự trùng khớp giữa hai bảng với nhau, giá trị không xác định (NULL) sẽ được trả về cho các cột của bảng chứa các giá trị thiếu.
Cú pháp của FULL JOIN:
SELECT *
FROM Customer AS CST
FULL JOIN CustomerAddress AS CTA
ON CST.CustomerID = CTA.CustomerID;
Kết hợp với nhiều hơn 2 bảng
JOIN nhiều hơn 2 bảng được phát triển từ phép JOIN thông thường. Thay vì chỉ JOIN 2 bảng, chúng ta cũng có thể JOIN nhiều hơn 2 bảng.
Cú pháp của JOIN nhiều hơn 2 bảng:
SELECT *
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.Key = t2.Key
JOIN Table3 AS t3 ON t1.Key2 = t3.Key2;
Ví dụ: Từ 3 bảng trong bộ dữ liệu FactInternetSales, DimProduct, DimCustomer thuộc bộ dữ liệu AdventureWorksDW2019, truy vấn các cột ProductKey, FirstName, Color, SalesAmount. Với điều kiện, các đơn hàng có bán sản phẩm có Color = ‘Red’ và Customer FirstName bắt đầu bằng chữ A.
SELECT FIS.ProductKey, DC.FirstName, DP.Color, FIS.SalesAmount
FROM dbo.FactInternetSales AS FIS
JOIN dbo.DimProduct AS DP ON FIS.ProductKey = DP.ProductKey
JOIN dbo.DimCustomer AS DC ON FIS.CustomerKey = DC.CustomerKey
WHERE DP.Color = 'Red' AND DC.FirstName LIKE 'A%';
Kết hợp với chính nó (Self Join)
Self Join về bản chất vẫn là phép Join thông thường, tuy nhiên thay vì kết hợp với một bảng khác, chúng ta sẽ sử dụng mối quan hệ về dữ liệu có sẵn ở trong bảng để Join với chính nó.
Self Join thường được sử dụng với dữ liệu có mối quan hệ phân cấp và phân tầng, ví dụ như dữ liệu mô hình tổ chức (Khối - Phòng ban), dữ liệu nhân sự (Cấp quản lý - Cấp trực thuộc),…
Cú pháp của Self Join:
SELECT e.FirstName AS EmployeeName, m.FirstName AS ManagerName
FROM dbo.DimEmployee AS e
LEFT JOIN dbo.DimEmployee AS m ON e.ParentEmployeeKey = m.EmployeeKey;
Ví dụ: Ta có bảng dữ liệu dbo.DimEmployee thuộc bộ dữ liệu AdventureWorksDW2020. Thực hiện truy vấn tên người quản lý tương ứng với từng nhân viên (Sử dụng Self Join).
SELECT e.FirstName AS EmployeeName, m.FirstName AS ManagerName
FROM dbo.DimEmployee AS e
LEFT JOIN dbo.DimEmployee AS m ON e.ParentEmployeeKey = m.EmployeeKey;
Các lưu ý khi viết JOIN
Lưu ý 1: Về Alias tên bảng
Khi thực hiện JOIN, ngầm định sẽ phải cần Alias tên bảng để kết hợp. Khi Alias, hãy sử dụng các tên viết tắt và mang tính gợi nhớ đến bảng gốc.
SELECT CST.CustomerID, CTA.AddressID
FROM Customer AS CST
JOIN CustomerAddress AS CTA
ON CST.CustomerID = CTA.CustomerID;
Lưu ý 2: Lỗi Ambigous column name
Đây là một lỗi thường xuyên gặp khi mới thực hành viết phép JOIN. Nguyên nhân lỗi này từ việc gọi tên cột CustomerID nhưng tên cột này xuất hiện ở cả 2 bảng Customer và CustomerAddress. Vậy nên cần khai báo rõ ràng cột CustomerID đến từ bảng nào bằng cách khai báo tên bảng Alias đằng trước tên cột (Ví dụ: CST.CustomerID).
SELECT CST.CustomerID, CTA.AddressID
FROM Customer AS CST
JOIN CustomerAddress AS CTA
ON CST.CustomerID = CTA.CustomerID;
Sau khi viết lại thành CST.CustomerID thì không còn lỗi như trước. Ngoài ra, đối với cột FirstName tuy không có tên Alias của bảng đằng trước nhưng không bị lỗi Ambigous là vì cột FirstName chỉ tồn tại duy nhất ở bảng Customer (CST).
Mặc dù vậy, vẫn khuyến khích sử dụng tên Alias bảng kèm đằng trước các cột để câu lệnh rõ ràng và mạch lạc hơn.
Kết hợp bảng sử dụng UNION
UNION và UNION ALL
UNION
UNION kết hợp các cột từ hai hay nhiều câu lệnh SELECT theo chiều dọc và không bao gồm các dòng trùng lặp.
Đặc điểm:
- Số cột dữ liệu của các câu lệnh SELECT cần phải bằng nhau.
- Các cột tương ứng cần có cùng kiểu dữ liệu.
- UNION sẽ gộp cả giá trị NULL.
Cú pháp của UNION:
SELECT *
FROM PurchaseOrderHeader
UNION
SELECT *
FROM SalesOrderHeader;
UNION ALL
Tương tự như UNION, UNION ALL kết hợp cột từ hai hay nhiều câu lệnh SELECT theo chiều dọc. Tuy nhiên, không loại bỏ các dòng trùng lặp nếu có.
Đặc điểm:
- Số cột dữ liệu của các câu lệnh SELECT cần phải bằng nhau.
- Các cột tương ứng cần có cùng kiểu dữ liệu.
- UNION ALL sẽ gộp cả giá trị NULL.
Cú pháp của UNION ALL:
SELECT *
FROM PurchaseOrderHeader
UNION ALL
SELECT *
FROM SalesOrderHeader;
Một số lý thuyết tập hợp (Set theory) khác
Bên cạnh UNION và UNION ALL được giới thiệu chính, bạn có thể tham khảo thêm các lý thuyết tập hợp khác là INTERSECT và EXCEPT.
Intersect
INTERSECT truy vấn các bản ghi đồng thời xuất hiện trong 2 câu truy vấn đã chọn.
Đặc điểm:
- INTERSECT có thể trả lại giá trị không tồn tại (NULL).
- INTERSECT không trả lại các giá trị trùng lặp.
- Số cột dữ liệu của 2 bảng cần phải bằng nhau.
SELECT *
FROM SalesOrderHeader
INTERSECT
SELECT *
FROM PurchaseOrderHeader;
Exception
EXCEPT dùng để truy vấn ra các giá trị trong kết quả của câu truy vấn bảng 1 và loại trừ kết quả trùng khớp với câu truy vấn bảng 2.
SELECT *
FROM Product
EXCEPT
SELECT *
FROM ProductAndDescription;
Chúng ta đã học cách hợp nhất các bảng trong SQL bằng hai phương pháp khác nhau: JOIN và UNION. Tổng kết lại, JOIN cho phép chúng ta kết hợp dữ liệu dựa trên một cột hoặc điều kiện chung, trong khi UNION cho phép chúng ta kết hợp dữ liệu bằng cách nối thêm các hàng từ bảng này sang bảng khác. Hy vọng rằng bài đăng này đã giúp bạn hiểu những kiến thức cơ bản về việc hợp nhất các bảng trong SQL, giúp bạn chọn được phương pháp tốt nhất cho nhu cầu của mình. Nếu bạn có bất kỳ câu hỏi hoặc phản hồi nào, xin vui lòng để lại bình luận bên dưới. Cảm ơn các bạn độc giả thân yêu!