Toán tử tập hợp trong SQL: Hướng dẫn toàn diện

Các phép toán tập hợp trong SQL là gì?

Các phép toán tập hợp trong SQL là kỹ thuật để kết hợp hoặc so sánh kết quả của hai hay nhiều câu lệnh SELECT. Chúng hoạt động như các phép toán tập hợp trong toán học, cho phép chúng ta tìm hợp, giao hoặc hiệu giữa các hàng được trả về bởi truy vấn. Điều này khiến chúng trở nên không thể thiếu khi phân tích dữ liệu từ nhiều nguồn hoặc nhiều góc nhìn.

Dưới đây là tổng quan nhanh về các phép toán tập hợp cốt lõi:

• UNION: Gộp tất cả các hàng duy nhất từ hai hoặc nhiều câu lệnh SELECT, loại bỏ trùng lặp.

• UNION ALL: Gộp tất cả các hàng từ hai hoặc nhiều câu lệnh SELECT, giữ lại trùng lặp.

• INTERSECT: Chỉ trả về các hàng xuất hiện trong cả hai câu lệnh SELECT.

• EXCEPT: Trả về các hàng từ câu lệnh SELECT thứ nhất không xuất hiện trong câu lệnh thứ hai.

So sánh với các phép toán đại số quan hệ

Đại số quan hệ là một khung lý thuyết cung cấp nền tảng để hiểu các truy vấn cơ sở dữ liệu. Nó đưa ra các phép toán trừu tượng như chiếu, chọn và nối, bắt nguồn từ các nguyên lý toán học và độc lập với hệ thống cơ sở dữ liệu cụ thể. Hãy coi nó như logic “hậu trường” vận hành tương tác của chúng ta với cơ sở dữ liệu.

Các toán tử tập hợp trong SQL cung cấp cách triển khai thực tiễn các khái niệm này trong môi trường cơ sở dữ liệu. Chúng cho phép chúng ta thực hiện các phép hợp, giao và hiệu trực tiếp trên các tập kết quả của truy vấn SQL.

Trong khi đại số quan hệ cung cấp nền tảng hình thức cho các phép toán cơ sở dữ liệu, các toán tử tập hợp trong SQL mang đến một giao diện chuẩn hóa, thân thiện cho các nhiệm vụ thao tác dữ liệu.

Hiểu mối quan hệ giữa các toán tử tập hợp và các phép toán trong đại số quan hệ giúp soi sáng nền tảng lý thuyết của SQL. Biết được mối liên hệ này giúp bạn viết truy vấn với mô hình tinh thần rõ ràng về những gì diễn ra bên dưới.

Các loại toán tử tập hợp trong SQL

Có ba toán tử tập hợp chính trong SQL:

• UNION

• INTERSECT

• EXCEPT (hoặc MINUS trong một số biến thể)

Các toán tử này tương ứng về mặt toán học với khái niệm hợp, giao và hiệu tập hợp.

Quy tắc sử dụng toán tử tập hợp

Trước khi kết hợp truy vấn bằng toán tử tập hợp, cần đáp ứng bốn yêu cầu. Chỉ cần sai một điều, truy vấn của bạn sẽ báo lỗi.

• Cùng số lượng cột: Mỗi SELECT phải trả về cùng số cột. Nếu một truy vấn trả về ba cột và truy vấn khác trả về hai, cơ sở dữ liệu sẽ ném lỗi.

• Kiểu dữ liệu tương thích: Các cột tương ứng phải có kiểu phù hợp. Bạn không thể kết hợp cột tên kiểu VARCHAR với cột ID kiểu INTEGER ở cùng vị trí.

• Thứ tự cột giống nhau: SQL ánh xạ cột theo vị trí, không theo tên. Cột thứ nhất của truy vấn một ánh xạ tới cột thứ nhất của truy vấn hai, bất kể tên cột là gì.

• ORDER BY chỉ ở cuối: ORDER BY phải xuất hiện một lần, ở cuối cùng của truy vấn kết hợp. Bạn không thể dùng nó bên trong từng câu lệnh SELECT riêng lẻ.

Một chi tiết nữa: tên cột trong kết quả đầu ra lấy từ câu lệnh SELECT đầu tiên. Bí danh ở các truy vấn sau sẽ bị bỏ qua, vì vậy nếu cần tên cột tùy chỉnh, hãy chỉ thêm bí danh vào truy vấn đầu tiên.

Cách dùng toán tử UNION trong SQL

Toán tử UNION kết hợp kết quả của hai hoặc nhiều truy vấn SELECT thành một tập kết quả duy nhất, mặc định loại bỏ các hàng trùng lặp.

Ví dụ, giả sử chúng ta có hai bảng, employees và contractors, mỗi bảng có các cột tương tự như contractors, department và salary. Vì mục đích học tập, hãy xem hai bảng giả lập này:

employees:

contractors:

Chúng ta có thể kết hợp kết quả từ cả hai bảng bằng lệnh sau:

-- Using UNION to combine all employees and contractors
SELECT name, department, salary FROM employees
UNION
SELECT name, department, salary FROM contractors;

Truy vấn này chọn các cột name, department và salary từ cả hai bảng employees và contractors rồi kết hợp chúng thành một tập kết quả duy nhất. Toán tử UNION tự động loại bỏ các hàng trùng lặp khỏi kết quả cuối.

Lưu ý rằng Carol, người xuất hiện ở cả hai bảng, chỉ được liệt kê một lần trong kết quả. Nếu muốn giữ cả hai trường hợp của Carol (với mức lương khác nhau), chúng ta sẽ dùng UNION ALL.

UNION vs UNION ALL

Toán tử UNION không loại bỏ các giá trị NULL. Nếu một cột chứa giá trị NULL trong một tập kết quả và cột tương ứng trong tập kết quả khác chứa giá trị không-NULL, các giá trị NULL sẽ được giữ lại trong tập kết quả cuối cùng do UNION tạo ra.

Nếu chúng ta muốn bao gồm các giá trị NULL trong tập kết quả và ngăn việc chúng bị loại bởi UNION, có thể dùng toán tử UNION ALL. Toán tử này kết hợp kết quả của nhiều truy vấn SELECT, bao gồm tất cả các hàng từ mỗi tập kết quả, bất kể có trùng lặp hay chứa NULL.

Cách dùng toán tử INTERSECT trong SQL

Toán tử INTERSECT chỉ trả về các hàng xuất hiện trong cả hai tập kết quả. Hãy coi nó như việc tìm những người thuộc cả hai nhóm.

Hãy dùng INTERSECT để truy vấn các bảng trên. Vì mục đích ví dụ, ta chỉ truy vấn cột name và department:

-- Using INTERSECT to find common employees
SELECT name, department FROM employees
INTERSECT
SELECT name, department FROM contractors;

Truy vấn này chọn các cột name và department từ cả hai bảng employees và contractors và chỉ trả về những hàng tồn tại ở cả hai bảng dựa trên tất cả các cột được chọn.

Toán tử INTERSECT xử lý các giá trị NULL theo các quy tắc so sánh chuẩn, coi các giá trị NULL là bằng nhau khi so sánh các cột tương ứng. Nó cũng cho kết quả rỗng khi làm việc với các tập kết quả rỗng.

Nói cách khác, nếu một giá trị NULL có trong một tập kết quả và cột tương ứng trong tập kết quả kia chứa giá trị không-NULL, các hàng sẽ không được coi là bằng nhau – chúng sẽ không nằm trong kết quả giao.

Ngoài ra, nếu một trong các tập kết quả đưa vào toán tử INTERSECT là rỗng, kết quả tổng thể cũng rỗng. Không có hàng chung nào giữa một tập rỗng và bất kỳ thứ gì khác.

Cách dùng toán tử UNION trong SQL

Toán tử EXCEPT trả về các hàng từ tập kết quả thứ nhất mà không có trong tập thứ hai.

Trong Oracle Database, EXCEPT được viết là MINUS; các biến thể lớn khác (PostgreSQL, SQL Server, MySQL 8.0.31+, SQLite) dùng EXCEPT.

Ví dụ, giả sử chúng ta chạy truy vấn sau:

-- Using EXCEPT to find employees who are not contractors
SELECT name, department, salary FROM employees
EXCEPT
SELECT name, department, salary FROM contractors;

Các cột name, department và salary được chọn từ bảng employees và chỉ trả về các hàng không tồn tại trong bảng contractors.

Toán tử EXCEPT cũng tuân theo các quy tắc so sánh chuẩn khi xử lý giá trị NULL. Hành vi của nó với các tập kết quả rỗng sẽ cho ra tập rỗng nếu tập thứ nhất rỗng, hoặc bao gồm toàn bộ hàng từ tập thứ nhất nếu tập thứ hai rỗng.

Toán tử tập hợp trong SQL: Hiệu năng và tối ưu hóa

Ảnh hưởng của các toán tử tập hợp đến hiệu năng truy vấn trong SQL có thể khác nhau tùy thuộc vào các yếu tố như kích thước tập dữ liệu, độ phức tạp của truy vấn và hệ quản trị cơ sở dữ liệu (DBMS) sử dụng.

Hãy phân tích các yếu tố chính và chiến lược tối ưu hóa.

Khối lượng dữ liệu và độ phức tạp truy vấn

Khi làm việc với lượng dữ liệu lớn, các toán tử tập hợp có thể tác động đáng kể đến hiệu năng vì kích thước các tập kết quả cần kết hợp, giao hoặc so sánh làm tăng thời gian xử lý cần thiết để thực hiện phép toán đó.

Các truy vấn phức tạp chứa nhiều truy vấn lồng, phép nối hoặc toán tử tập hợp có thể dẫn đến chi phí xử lý bổ sung và ảnh hưởng đến hiệu năng. Các phép toán xâu chuỗi hoặc lồng nhau có thể làm trầm trọng thêm hậu quả về hiệu năng.

Đánh chỉ mục và kỹ thuật tối ưu hóa

Đánh chỉ mục đúng cho các cột tham gia phép toán tập hợp có thể cải thiện đáng kể hiệu năng truy vấn. Chỉ mục giúp bộ máy cơ sở dữ liệu nhanh chóng xác định và truy xuất các hàng liên quan, giảm nhu cầu quét toàn bộ bảng và cải thiện thời gian thực thi.

Để cải thiện hiệu năng các truy vấn có dùng toán tử tập hợp, quản trị viên và nhà phát triển có thể áp dụng các kỹ thuật như viết lại truy vấn, phân tích kế hoạch thực thi và tối ưu hóa lược đồ. Các kỹ thuật như bộ nhớ đệm truy vấn và vật chất hóa view cũng có thể được dùng để tính trước và lưu trữ kết quả của các truy vấn phức tạp, giảm chi phí tính toán của phép toán tập hợp.

Bộ máy cơ sở dữ liệu và tài nguyên phần cứng

Hiệu năng các phép toán tập hợp có thể khác nhau tùy thuộc vào bộ máy cơ sở dữ liệu nền tảng và khả năng tối ưu hóa của nó. Các DBMS khác nhau có thể dùng các chiến lược và thuật toán tối ưu hóa khác nhau khi xử lý phép toán tập hợp, dẫn đến khác biệt về hiệu năng.

Sự sẵn có của tài nguyên phần cứng như CPU, bộ nhớ và I/O đĩa cũng ảnh hưởng đến hiệu năng của các truy vấn có dùng toán tử tập hợp. Tài nguyên phần cứng đầy đủ có thể giúp giảm nút thắt hiệu năng và đảm bảo thực thi truy vấn hiệu quả.

Toán tử tập hợp trong thực tiễn

Các toán tử tập hợp không chỉ là công cụ lý thuyết; chúng có các ứng dụng thực tế có thể ảnh hưởng đáng kể đến quyết định kinh doanh. Hãy cùng đi qua một ví dụ đơn giản về cách một công ty có thể dùng chúng để phân khúc tập khách hàng cho các chiến dịch tiếp thị theo mục tiêu.

Kịch bản

Hãy hình dung một công ty bán hàng cả trực tuyến và tại cửa hàng. Họ có hai tập dữ liệu riêng:

1. Mua hàng trực tuyến: ID khách hàng, lịch sử mua, nhân khẩu học và vị trí cho người mua online.
2. Giao dịch tại cửa hàng: Thông tin tương tự cho khách mua trực tiếp.

Sử dụng các toán tử tập hợp

Để có bức tranh đầy đủ về toàn bộ khách hàng, công ty sẽ dùng UNION để kết hợp hai tập dữ liệu vào một bảng, loại bỏ trùng lặp. Điều này cho họ một cái nhìn hợp nhất về toàn bộ tập khách hàng.

Tiếp theo, họ có thể dùng INTERSECT để xác định khách đã mua cả online lẫn tại cửa hàng. Phân khúc này đặc biệt giá trị vì họ tương tác cao với thương hiệu trên nhiều kênh.

Để tìm cơ hội khuyến khích mua sắm đa kênh, công ty có thể dùng EXCEPT. Ví dụ, SELECT * FROM online_purchases EXCEPT SELECT * FROM in_store_transactions sẽ tìm những khách chỉ mua online mà chưa mua tại cửa hàng. Khi đó công ty có thể nhắm nhóm này bằng các khuyến mãi khuyến khích đến cửa hàng.

Vượt ra ngoài phân khúc

Khi đã xác định các phân khúc, công ty có thể đi xa hơn, tinh chỉnh dựa trên các yếu tố bổ sung như nhân khẩu học hoặc lịch sử mua. Sự thấu hiểu chi tiết này cho phép họ điều chỉnh chiến dịch tiếp thị chính xác hơn.

Toán tử tập hợp so với JOIN

Cả toán tử tập hợp và SQL JOIN đều kết hợp dữ liệu từ nhiều truy vấn, nhưng cách hoạt động khác nhau. Câu hỏi cốt lõi là bạn muốn kết hợp hàng hay cột.

Hạn chế và lưu ý khi dùng toán tử tập hợp

Khi dùng các toán tử tập hợp trong SQL, cần cân nhắc một số hạn chế và yếu tố có thể ảnh hưởng đến hiệu năng truy vấn, độ chính xác kết quả và tính dễ dùng tổng thể.

Tương thích kiểu dữ liệu và giá trị NULL

Các cột tương ứng trong các tập kết quả phải có kiểu dữ liệu tương thích. Hãy kiểm tra các cột tương ứng có kiểu tương thích trước khi kết hợp truy vấn. Không khớp kiểu gây lỗi và dễ bị bỏ sót.

Các toán tử tập hợp có thể xử lý giá trị NULL khác nhau tùy DBMS và toán tử cụ thể. Để tránh lỗi, nhà phát triển cần hiểu cách NULL được xử lý.

Tác động hiệu năng và hàng trùng lặp

Các phép toán tập hợp có thể ảnh hưởng đáng kể đến hiệu năng truy vấn, đặc biệt khi xử lý tập dữ liệu lớn hoặc phức tạp. Các yếu tố như đánh chỉ mục, tối ưu truy vấn và tài nguyên phần cứng có thể chi phối hiệu năng. Các kỹ thuật tối ưu và tinh chỉnh hiệu năng là cần thiết để giảm nút thắt.

Mặc định, các toán tử tập hợp loại bỏ hàng trùng khỏi kết quả. Tuy nhiên, trong một số trường hợp, việc giữ hàng trùng có thể cần thiết. Điều quan trọng là hiểu hành vi của các toán tử đối với hàng trùng và dùng kỹ thuật phù hợp để xử lý khi cần.

Thứ tự kết quả và giới hạn bộ nhớ

Các toán tử tập hợp không đảm bảo thứ tự kết quả trong đầu ra cuối cùng. Để sắp xếp kết quả kết hợp, hãy thêm mệnh đề ORDER BY ở cuối toàn bộ truy vấn, sau câu lệnh SELECT cuối cùng.

Các phép toán tập hợp có thể tiêu tốn nhiều bộ nhớ và tài nguyên, đặc biệt khi xử lý tập dữ liệu lớn. Cần cân nhắc các giới hạn bộ nhớ và tài nguyên để tránh suy giảm hiệu năng hoặc hệ thống không ổn định.

Độ phức tạp, khả năng bảo trì và tính tương thích giữa các DBMS

Các truy vấn phức tạp có nhiều toán tử tập hợp, truy vấn lồng và phép nối có thể khó hiểu, khó bảo trì và gỡ lỗi. Để cải thiện khả năng đọc và bảo trì, truy vấn cần ngắn gọn, có tài liệu tốt và có cấu trúc mô-đun.

Các toán tử tập hợp có thể có khác biệt về cú pháp và hành vi giữa các hệ quản trị cơ sở dữ liệu (DBMS). Biết các khác biệt này là rất quan trọng khi viết truy vấn SQL nhằm đảm bảo khả năng tương thích đa nền tảng.

Kết luận

Các toán tử tập hợp giải quyết một bài toán cụ thể: kết hợp hoặc so sánh các tập kết quả khi chưa biết trước hàng nào trùng nhau.

Chúng bao gồm UNION, INTERSECT và EXCEPT: ba toán tử để kết hợp, so sánh và trừ các tập kết quả.

Nếu bạn muốn học thêm, hãy xem khóa học về Joining Data in SQL.