Cả hai toán tử đều xếp chồng kết quả của hai truy vấn lên nhau, vì vậy SQL UNION so với UNION ALL có thể giống như một lựa chọn nhỏ. Không phải vậy. Một từ bổ sung sẽ thay đổi cả kết quả của bạn và mức độ hoạt động của cơ sở dữ liệu.

Chúng tôi đã chạy mọi truy vấn ở đây bằng SQLite 3.50 và kết quả cũng như thời gian hiển thị đều đến từ những lần chạy đó. Hành vi cơ bản là SQL tiêu chuẩn trên các cơ sở dữ liệu chính. Tuy nhiên, thời gian sẽ khác nhau tùy theo cơ sở dữ liệu, dữ liệu và phần cứng của riêng bạn, vì vậy hãy coi thời gian của chúng tôi là minh họa.

Câu trả lời ngắn gọn

UNION kết hợp hai tập hợp kết quả và chỉ trả về các hàng riêng biệt. UNION ALL kết hợp chúng và trả về mọi thứ, kể cả các bản sao. Đó là toàn bộ sự khác biệt và phần còn lại của bài viết này là về lý do tại sao nó lại quan trọng.

UNION vs UNION ALL cạnh nhau

Dữ liệu mẫu

Hãy tưởng tượng một cửa hàng lưu giữ các đơn đặt hàng trực tuyến và tại cửa hàng trong các bảng riêng biệt. Lưu ý rằng Asha có hai đơn hàng trực tuyến giống hệt nhau và một đơn hàng giống hệt nhau tại cửa hàng.

CREATE TABLE online_orders (customer TEXT, total INT);
CREATE TABLE store_orders (customer TEXT, total INT);

INSERT INTO online_orders VALUES ('Asha',100), ('Ben',200), ('Asha',100);
INSERT INTO store_orders VALUES ('Asha',100), ('Dev',300);

Xem sự khác biệt

SELECT customer, total FROM online_orders
UNION
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Dev  | 300

SELECT customer, total FROM online_orders
UNION ALL
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Asha | 100
-- Asha | 100
-- Dev  | 300

Năm hàng đầu vào trở thành ba hàng có UNION và cả năm hàng vẫn giữ nguyên UNION ALL. Hai điều cần chú ý:

• UNION đã xóa các kết quả trùng lặp khỏi toàn bộ kết quả, bao gồm cả những kết quả trong bảng đầu tiên. Nó không chỉ so sánh giữa các bảng.
• Một hàng chỉ được tính là trùng lặp khi mọi cột đã chọn khớp.

Khi bạn thực sự muốn có một danh sách riêng biệt, UNION hoàn toàn chính xác. Điều này mang lại cho mọi khách hàng đã mua qua một trong hai kênh:

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

Lỗi tổng thể sai

Đây là lý do tại sao sự lựa chọn thực sự quan trọng. Giả sử bạn muốn tổng doanh thu. Các đơn hàng trên cộng lại lên tới 800. Hãy xem mỗi nhà điều hành đưa ra những gì:

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION
  SELECT total FROM store_orders
);
-- 600

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION ALL
  SELECT total FROM store_orders
);
-- 800

Phiên bản UNION sai 200. Vì chúng tôi chỉ chọn cột total nên ba đơn hàng 100 rupee riêng biệt trông giống như hai bản sao và bị thu gọn thành một. Không có thông báo lỗi và 600 có vẻ là một con số hoàn toàn có thể tin được. Loại lỗi thầm lặng này có thể tồn tại trong báo cáo tài chính trong nhiều tháng.

Quy tắc cả hai phải tuân theo

Cả hai nhà khai thác đều có cùng yêu cầu:

1. Mỗi SELECT phải trả về cùng số cột.
2. Các cột được khớp theo vị trí chứ không phải theo tên, vì vậy hãy giữ chúng theo cùng thứ tự.
3. Loại dữ liệu ở mỗi vị trí phải tương thích.
4. Tên cột kết quả đến từ đầu tiên SELECT.
5. ORDER BY nằm ở cuối và áp dụng cho toàn bộ kết quả.

Phá vỡ quy tắc đầu tiên sẽ gây ra lỗi. Đây là thông báo từ SQLite:

SELECT customer, total FROM online_orders
UNION
SELECT customer FROM store_orders;

-- SELECTs to the left and right of UNION do not have
-- the same number of result columns

Quy tắc thứ hai nguy hiểm hơn vì nó có thể thất bại mà không có lỗi. Nếu bạn hoán đổi hai cột cùng loại, chẳng hạn như first_name và last_name, truy vấn sẽ chạy và dữ liệu sẽ hiển thị sai. SQLite cũng thoải mái về các loại, trong khi các cơ sở dữ liệu như PostgreSQL chặt chẽ hơn và có thể yêu cầu một cách rõ ràng CAST.

Dán nhãn nguồn

Một thủ thuật rất thực tế là thêm một cột cố định cho biết mỗi hàng đến từ đâu. Vì nhãn khác nhau nên các hàng từ các bảng khác nhau không thể được coi là trùng lặp với nhau nữa.

SELECT customer, total, 'online' AS channel FROM online_orders
UNION ALL
SELECT customer, total, 'store' FROM store_orders
ORDER BY customer, channel;

-- Asha | 100 | online
-- Asha | 100 | online
-- Asha | 100 | store
-- Ben  | 200 | online
-- Dev  | 300 | store

Sau khi dữ liệu của bạn có nhãn, bạn có thể nhóm dữ liệu đó để báo cáo doanh số bán hàng theo kênh bằng cách sử dụng GROUP BY. Nếu bạn cần kết hợp nhiều bước, hãy gói phần kết hợp trong CTE để giữ cho nó có thể đọc được.

Hiệu suất: điều gì đã xảy ra trong thử nghiệm của chúng tôi

Để loại bỏ các bản sao, cơ sở dữ liệu phải so sánh các hàng, thường bằng cách sắp xếp hoặc băm chúng. UNION ALL có thể bỏ qua bước này. Chúng tôi đã tạo hai bảng, mỗi bảng có 500.000 hàng, chứa các số ngẫu nhiên từ 0 đến 50.000, do đó có rất nhiều bản sao. Chúng tôi đã chạy từng truy vấn nhiều lần và giữ được thời gian tốt nhất.

Đầu tiên, chúng tôi đếm kết quả để thời gian gửi hàng trở lại chương trình của chúng tôi không ảnh hưởng đến việc so sánh:

Khoảng cách lớn một phần là do SQLite có thể tính toán số lượng đó mà không cần đọc từng hàng, vì vậy vui lòng không đọc nó là “nhanh hơn hàng nghìn lần”. Tuy nhiên, nó thể hiện ý tưởng cốt lõi: UNION phải làm công việc thực tế còn UNION ALL thì không.

Sau đó, chúng tôi tìm nạp tất cả các hàng vào Python và kết quả như sau:

Ở đây, UNION ALL mất nhiều thời gian hơn vì nó phân phối số hàng gấp 20 lần. Sự trùng lặp không phải là nguyên nhân làm chậm nó. Xử lý một triệu hàng là. Vì vậy, quy tắc trung thực là UNION ALL sẽ rẻ hơn để tính toán cơ sở dữ liệu, nhưng kích thước của kết quả cũng quan trọng. Nhiều biểu đồ trực tuyến giảm điều này thành “UNION ALL luôn nhanh hơn” và thử nghiệm của chúng tôi cho thấy lý do tại sao nó không đơn giản như vậy.

Đừng chọn chỉ dựa trên tốc độ. Chọn toán tử đưa ra câu trả lời đúng trước, sau đó điều chỉnh. Nếu truy vấn chậm, hãy đọc hướng dẫn của chúng tôi về Tối ưu hóa truy vấn SQL.

Khi nào nên sử dụng mỗi

Sử dụng UNION ALL khi:

• Bạn đang hợp nhất dữ liệu từ các bảng tương tự, chẳng hạn như phân vùng hàng tháng.
• Bạn sẽ tính tổng hoặc đếm sau đó.
• Bạn biết đầu vào không thể chồng lên nhau.
• Các bản sao có ý nghĩa, như trong nhật ký hoặc hồ sơ kiểm toán.

Sử dụng UNION khi:

• Bạn cần một danh sách duy nhất, chẳng hạn như mọi địa chỉ email trên hai hệ thống.
• Các nguồn chồng chéo chứa cùng một thông tin nhiều lần.

Hãy coi UNION ALL là mặc định và sử dụng UNION một cách có chủ ý. Việc thêm mệnh đề WHERE vào mỗi nhánh để lọc sớm sẽ giúp ích cho cả hai.

GIAO NHẬN VÀ NGOẠI TRỪ

Hai toán tử tập hợp có liên quan trả lời các câu hỏi khác nhau. Cả hai đều tuân theo các quy tắc cột giống nhau:

SELECT customer FROM online_orders
INTERSECT
SELECT customer FROM store_orders;
-- Asha  (bought in both channels)

SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders;
-- Ben   (online only)

Theo truyền thống, Oracle sử dụng MINUS thay vì EXCEPT và việc hỗ trợ các toán tử này thay đổi một chút tùy theo cơ sở dữ liệu và phiên bản.

ĐOÀN vs THAM GIA

Chúng thường bị nhầm lẫn nhưng chúng kết hợp dữ liệu theo các hướng khác nhau:

Những lỗi thường gặp

• Sử dụng UNION theo thói quen. Việc này có thể khiến bạn mất tốc độ hoặc xóa các hàng hợp lệ.
• Chọn quá ít cột. Việc bỏ ID sẽ khiến các hàng riêng biệt trông giống hệt nhau.
• Thứ tự cột không khớp. Các giá trị có thể nằm sai cột mà không có bất kỳ lỗi nào.
• Thêm ORDER BY vào mỗi nhánh. Sắp xếp một lần vào cuối.
• Sử dụng UNION để vá một liên kết xấu. Nếu các bản sao xuất hiện do một liên kết, hãy sửa liên kết đó.
• Giả sử thứ tự kết quả. Nếu không có ORDER BY, thứ tự hàng không được đảm bảo cho cả hai toán tử.

Lời kết

Mỗi lần hỏi một câu: các hàng giống hệt nhau là cùng một sự kiện hay các sự kiện riêng biệt? Nếu chúng tách biệt hoặc không thể chồng lên nhau, hãy sử dụng UNION ALL. Nếu bạn cần một danh sách duy nhất, hãy sử dụng UNION. Để kiểm tra các báo cáo của riêng bạn, hãy tìm kiếm mã UNION gần SUM hoặc COUNT và xác nhận rằng những tổng số đó thực sự đúng.

Câu hỏi thường gặp

Tiếp tục học hỏi