Toán tử UNION trong PostgreSQL

Tình huống mở đầu

Cà Phê Chú Tư có hai tờ danh sách khách muốn nhận phiếu giảm giá. Tờ thứ nhất là 6 dòng đăng ký qua app, tờ thứ hai là 5 dòng ghi tay ở quầy. Chú Tư muốn nhắn tin cho từng người đúng một lần. Chú nối hai tờ bằng UNION ALL và nhận 11 dòng. Khách tên Binh có tới 5 dòng, nghĩa là sáng mai Binh nhận 5 tin nhắn giống hệt nhau và gọi điện hỏi Chú Tư có bị lỗi không.

Hãy đoán trước hai điều. Một, đổi UNION ALL thành UNION thì còn mấy dòng. Hai, nếu danh sách có khách chưa để số điện thoại (NULL), những dòng đó có bị coi là trùng nhau không.

Ôn nhanh từ chương trước: biểu thức NULL = NULL trả về TRUE, FALSE hay NULL?

Đáp án: NULL, vì so sánh với một giá trị chưa biết thì kết quả cũng chưa biết. Bài này cho thấy một chỗ SQL đối xử với NULL khác hẳn quy tắc đó.

Mục tiêu bài học

Sau bài này bạn có thể:

• Viết UNION và UNION ALL, dự đoán đúng số dòng và biết khi nào cần loại trùng.
• Giải thích UNION so sánh nguyên dòng chứ không so sánh từng cột, và coi hai giá trị NULL là giống nhau.
• Đọc lỗi 42601 (lệch số cột) và 42804 (lệch kiểu cột), biết tên cột kết quả lấy từ vế đầu.
• Đặt ORDER BY và LIMIT đúng chỗ: cho cả kết quả hay cho riêng từng vế.

Bức tranh lớn

Xuyên suốt bài, mỗi bảng là một tờ danh sách giấy. UNION ALL là dán hai tờ nối nhau. UNION là dán nối rồi lấy bút gạch những dòng viết giống hệt dòng đã có. Dự án xuyên suốt cả chương là Cà Phê Chú Tư, database shop, với hai tờ danh sách:

CREATE TABLE app_customers (
  email text NOT NULL,
  phone text
);
CREATE TABLE store_customers (
  email text NOT NULL,
  phone text
);
INSERT INTO app_customers VALUES
  ('an@mail.com', '0901'),
  ('binh@mail.com', NULL),
  ('binh@mail.com', NULL),
  ('binh@mail.com', NULL),
  ('chi@mail.com', '0903'),
  ('dung@mail.com', NULL);
INSERT INTO store_customers VALUES
  ('binh@mail.com', NULL),
  ('binh@mail.com', NULL),
  ('chi@mail.com', '0903'),
  ('em@mail.com', '0905'),
  ('gia@mail.com', NULL);

Hình dưới trả lời một câu hỏi: từ hai câu SELECT, kết quả cuối cùng được tạo ra qua những bước nào?

Cách đọc hình: đọc từ hai ô xanh lơ ở trên, là hai vế SELECT. Chúng đổ vào ô xanh dương Append, nơi hai kết quả được nối thành 11 dòng. Ô cam hỏi bạn dùng UNION hay UNION ALL. Nhánh UNION ALL ra thẳng 11 dòng. Nhánh UNION đi qua ô tím loại dòng giống hệt rồi ra 6 dòng.

Chỗ ẩn dụ không còn đúng: người gạch tay chỉ gạch những dòng giống hệt bằng mắt thường. SQL so sánh từng giá trị trong dòng, và gặp NULL thì dùng một quy tắc riêng (xem phần Khái niệm cốt lõi).

Khái niệm cốt lõi

Bài này có bốn khái niệm.

1. UNION ALL nối, UNION nối rồi loại trùng

vế1 UNION ALL vế2 trả mọi dòng của hai vế, giữ nguyên số lần xuất hiện. vế1 UNION vế2 trả cùng các dòng đó rồi bỏ những dòng giống hệt nhau, nên mỗi dòng khác biệt chỉ còn một lần. Viết UNION DISTINCT cũng được, nghĩa giống hệt UNION. Hai vế phải là hai câu SELECT độc lập, mỗi vế tự có FROM và WHERE của mình.

2. Loại trùng so sánh nguyên dòng, và coi NULL là bằng nhau

Hai dòng bị coi là trùng khi mọi cột đều giống nhau. Chỉ cần một cột khác là hai dòng cùng ở lại. Khi so sánh để loại trùng, hai giá trị NULL được coi là giống nhau, dù NULL = NULL ở trong WHERE cho NULL. Đây là ngoại lệ có chủ đích của SQL: DISTINCT, GROUP BY và các toán tử tập hợp đều dùng quy tắc này.

3. Cột được ghép theo vị trí, kiểu phải hợp nhau

Cột thứ nhất của hai vế ghép với nhau, cột thứ hai ghép với nhau, bất kể tên cột. Số cột hai vế phải bằng nhau. Kiểu của từng cặp cột phải tìm được một kiểu chung (ví dụ integer và numeric thành numeric). Tên cột của kết quả lấy từ vế đầu tiên.

4. ORDER BY và LIMIT thuộc về cả kết quả

ORDER BY và LIMIT viết sau vế cuối cùng áp dụng cho toàn bộ kết quả đã gộp. Muốn sắp xếp hay cắt riêng từng vế, đặt vế đó trong ngoặc. ORDER BY ở cuối chỉ nhận tên cột kết quả hoặc số thứ tự cột, không nhận biểu thức như upper(email).

Ví dụ đầu tiên

Ví dụ nhỏ nhất: nối email của hai danh sách, giữ mọi dòng.

SELECT email
FROM app_customers
UNION ALL
SELECT email
FROM store_customers
ORDER BY email;

Dự đoán trước khi xem kết quả: binh@mail.com xuất hiện mấy lần?

Kết quả [Đã chạy: PostgreSQL 16.15]:

     email     
---------------
 an@mail.com
 binh@mail.com
 binh@mail.com
 binh@mail.com
 binh@mail.com
 binh@mail.com
 chi@mail.com
 chi@mail.com
 dung@mail.com
 em@mail.com
 gia@mail.com
(11 rows)

binh xuất hiện 5 lần (3 lần ở app, 2 lần ở quầy) và chi 2 lần. Đây chính là lý do Binh nhận 5 tin nhắn. Đổi UNION ALL thành UNION [Đã chạy: PostgreSQL 16.15]:

SELECT email
FROM app_customers
UNION
SELECT email
FROM store_customers
ORDER BY email;

     email     
---------------
 an@mail.com
 binh@mail.com
 chi@mail.com
 dung@mail.com
 em@mail.com
 gia@mail.com
(6 rows)

Còn 6 dòng, mỗi người một lần. So sánh số dòng của hai cách trong một câu lệnh [Đã chạy: PostgreSQL 16.15]:

SELECT
  (SELECT count(*) FROM (SELECT email FROM app_customers UNION ALL SELECT email FROM store_customers) AS t) AS union_all_rows,
  (SELECT count(*) FROM (SELECT email FROM app_customers UNION SELECT email FROM store_customers) AS t) AS union_rows;

 union_all_rows | union_rows 
----------------+------------
             11 |          6
(1 row)

Chênh lệch 5 dòng là phần lặp bị bỏ. Bây giờ đến chuyện NULL. Trong WHERE, NULL = NULL không bao giờ đúng, nhưng UNION thì gộp hai NULL làm một [Đã chạy: PostgreSQL 16.15]:

SELECT NULL = NULL AS null_equals_null;

SELECT phone
FROM app_customers
UNION
SELECT phone
FROM store_customers
ORDER BY phone;

 null_equals_null 
------------------
 NULL
(1 row)

 phone 
-------
 0901
 0903
 0905
 NULL
(4 rows)

Dòng đầu cho thấy NULL = NULL ra NULL. Bảng bên dưới chỉ còn 4 dòng, trong đó NULL xuất hiện đúng một lần dù có 7 dòng không để số điện thoại (4 ở app, 3 ở quầy trong tổng 11 dòng). Loại trùng tìm các dòng không phân biệt được với nhau, và hai NULL không phân biệt được.

Chú ý: so sánh là so sánh nguyên dòng. Hai dòng cùng email nhưng khác số điện thoại thì không trùng [Đã chạy: PostgreSQL 16.15]:

SELECT email, phone
FROM app_customers
WHERE email = 'chi@mail.com'
UNION
SELECT 'chi@mail.com', '0904'
ORDER BY phone;

    email     | phone 
--------------+-------
 chi@mail.com | 0903
 chi@mail.com | 0904
(2 rows)

Chi hiện hai lần vì 0903 và 0904 khác nhau. Còn tên cột và kiểu của kết quả thì sao? Tên lấy từ vế đầu [Đã chạy: PostgreSQL 16.15]:

SELECT email AS contact, phone
FROM app_customers
UNION
SELECT email AS other_name, phone
FROM store_customers
ORDER BY contact
LIMIT 3;

    contact    | phone 
---------------+-------
 an@mail.com   | 0901
 binh@mail.com | NULL
 chi@mail.com  | 0903
(3 rows)

Tên other_name ở vế sau bị bỏ, và ORDER BY contact gọi cột bằng tên của vế đầu. Với kiểu, PostgreSQL chọn kiểu chung cho từng cặp cột [Đã chạy: PostgreSQL 16.15]:

SELECT pg_typeof(n) AS result_type, n
FROM (SELECT 1 AS n UNION SELECT 2.5) AS t
ORDER BY n;

 result_type |  n  
-------------+-----
 numeric     |   1
 numeric     | 2.5
(2 rows)

Số 1 (kiểu integer) và 2.5 (kiểu numeric) hợp thành numeric, nên cả cột có kiểu numeric và số 1 được đổi sang numeric trước khi so sánh.

Cuối cùng, hai kế hoạch thực thi cho thấy chi phí của việc loại trùng [Có thể khác nhau: kế hoạch tùy thống kê và phiên bản]:

EXPLAIN (COSTS OFF)
SELECT email FROM app_customers
UNION
SELECT email FROM store_customers;

EXPLAIN (COSTS OFF)
SELECT email FROM app_customers
UNION ALL
SELECT email FROM store_customers;

               QUERY PLAN                
-----------------------------------------
 HashAggregate
   Group Key: app_customers.email
   ->  Append
         ->  Seq Scan on app_customers
         ->  Seq Scan on store_customers
(5 rows)

            QUERY PLAN             
-----------------------------------
 Append
   ->  Seq Scan on app_customers
   ->  Seq Scan on store_customers
(3 rows)

Cả hai đều có Append để nối. Kế hoạch UNION có thêm HashAggregate với Group Key, tức là một bảng băm để gom các dòng giống nhau. Kế hoạch UNION ALL không có bước đó. Với dữ liệu lớn, bước này tốn bộ nhớ và thời gian (PostgreSQL cũng có thể chọn sắp xếp rồi bỏ dòng liền kề giống nhau).

Xem từng bước

Animation dưới đây chạy lại đúng hai danh sách, nối rồi loại trùng. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Bảng trace bằng chữ [Đã chạy: PostgreSQL 16.15, hai danh sách của bài]:

Bạn vừa làm được: nhìn hai danh sách và nói trước UNION ALL cho bao nhiêu dòng, UNION cho bao nhiêu dòng.

Concept Check

Dùng dữ liệu của bài: app có 6 dòng, quầy có 5 dòng, binh có 3 dòng ở app và 2 dòng ở quầy (phone NULL).

Câu 1. SELECT email FROM app_customers UNION SELECT email FROM store_customers trả bao nhiêu dòng?

• A. 11, vì UNION cũng giữ mọi dòng như UNION ALL, chỉ khác tên gọi.
• B. 10, vì chỉ dòng chi bị loại do xuất hiện ở cả hai danh sách.
• C. 6, vì mỗi email khác nhau chỉ còn đúng một dòng sau khi loại trùng.
• D. 4, vì UNION chỉ giữ các email xuất hiện ở đúng một danh sách.

Câu 2. Thêm một cột nhãn nguồn: SELECT email, 'app' AS source ... UNION SELECT email, 'store' .... Kết quả có bao nhiêu dòng?

• A. 6, vì UNION luôn gộp theo email và bỏ qua mọi cột còn lại.
• B. 8, vì binh và chi còn một dòng cho mỗi nhãn nguồn khác nhau.
• C. 11, vì nhãn nguồn làm mọi dòng của hai vế đều khác nhau.
• D. 4, vì chỉ các email đứng ở vế thứ nhất mới được giữ.

Câu 3. Danh sách phone của hai bảng (cột có nhiều NULL) qua UNION cho bao nhiêu dòng NULL?

• A. 7 dòng, vì mỗi giá trị NULL là một giá trị chưa biết riêng biệt.
• B. 0 dòng, vì NULL bị UNION tự động loại ra khỏi kết quả.
• C. 2 dòng, một dòng từ mỗi vế vì UNION gộp trong từng vế riêng.
• D. 1 dòng, vì khi loại trùng hai giá trị NULL được coi là giống nhau.

Đáp án và giải thích:

• Câu 1: C. A sai vì chỉ UNION ALL giữ mọi dòng. B sai vì binh cũng lặp nhiều lần. D mô tả phép khác (hiệu đối xứng), UNION không làm vậy.
• Câu 2: B. Mỗi nhãn tạo một nhóm riêng: app có an, binh, chi, dung và quầy có binh, chi, em, gia, tổng 8. A sai vì UNION so sánh nguyên dòng. C sai vì trong cùng một vế dòng lặp vẫn bị gộp.
• Câu 3: D. A sai vì khi loại trùng NULL không được coi là khác nhau. B sai vì UNION không loại NULL. C sai vì loại trùng chạy trên cả kết quả đã nối, không riêng từng vế.

Làm cùng mình

Chú Tư muốn gửi tin nhắn cho khách theo thứ tự chữ cái, mỗi người một tin, và hôm nay chỉ gửi cho 4 người đầu. Dùng chung hai danh sách của bài.

Bước 1. Vẽ ra giấy

Chép 11 email ra giấy, gạch những email lặp, rồi xếp theo chữ cái và khoanh 4 email đầu. Ghi dự đoán.

Bước 2. Điền chỗ trống

SELECT email
FROM app_customers
...                                  -- TODO 1: chọn toán tử để mỗi người chỉ một lần
SELECT email
FROM store_customers
...                                  -- TODO 2: sắp xếp theo email
...;                                 -- TODO 3: chỉ lấy 4 dòng đầu

Gợi ý cho chỗ trống thứ nhất: hai danh sách có người lặp, nên cần toán tử loại trùng. Gợi ý cho chỗ trống thứ hai và thứ ba: viết sau vế cuối, chúng áp dụng cho cả kết quả.

Bước 3. Chạy và đối chiếu

Chạy xong bạn sẽ thấy [Đã chạy] đúng 4 email bạn đã khoanh ở Bước 1. Đáp án ở mục Lời giải và đáp án.

Bước 4. Thêm nhãn nguồn

Chú Tư muốn biết mỗi người đến từ app hay quầy nên thêm cột 'app' và 'store' vào hai vế, vẫn dùng UNION. Dự đoán trước: kết quả có mấy dòng, và còn đủ "mỗi người một tin" không? Xem kết quả để so sánh.

Thử phá nó đi

Bốn lỗi người mới thường gặp với UNION [Đã chạy: PostgreSQL 16.15]:

Chạy từng lỗi để xem thông báo thật. Lệch số cột:

SELECT email FROM app_customers
UNION
SELECT email, phone FROM store_customers;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 42601
ERROR:  each UNION query must have the same number of columns
LINE 3: SELECT email, phone FROM store_customers;
               ^

Lệch kiểu:

SELECT email FROM app_customers
UNION
SELECT 1;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 42804
ERROR:  UNION types text and integer cannot be matched
LINE 3: SELECT 1;
               ^

LIMIT đặt sai chỗ:

SELECT email FROM app_customers ORDER BY email LIMIT 2
UNION
SELECT email FROM store_customers;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 42601
ERROR:  syntax error at or near "UNION"
LINE 2: UNION
        ^

ORDER BY bằng biểu thức:

SELECT email FROM app_customers
UNION
SELECT email FROM store_customers
ORDER BY upper(email);
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 0A000
ERROR:  invalid UNION/INTERSECT/EXCEPT ORDER BY clause
LINE 4: ORDER BY upper(email);
                 ^
DETAIL:  Only result column names can be used, not expressions or functions.
HINT:  Add the expression/function to every SELECT, or move the UNION into a FROM clause.

Muốn giới hạn riêng từng vế thì dùng ngoặc. Lấy hai email cuối của app và hai email đầu của quầy [Đã chạy: PostgreSQL 16.15]:

(SELECT email FROM app_customers ORDER BY email DESC LIMIT 2)
UNION ALL
(SELECT email FROM store_customers ORDER BY email LIMIT 2)
ORDER BY email;

     email     
---------------
 binh@mail.com
 binh@mail.com
 chi@mail.com
 dung@mail.com
(4 rows)

Còn ORDER BY ... LIMIT đặt sau vế cuối thì áp dụng cho cả kết quả đã gộp [Đã chạy: PostgreSQL 16.15]:

SELECT email
FROM app_customers
UNION
SELECT email
FROM store_customers
ORDER BY email DESC
LIMIT 2;

    email     
--------------
 gia@mail.com
 em@mail.com
(2 rows)

Hiểu lầm quan trọng nhất của bài là nghĩ LIMIT đứng sau vế nào thì thuộc về vế đó. Hình dưới đối chiếu mô hình sai với mô hình đúng.

Cách đọc hình: hai chuỗi độc lập. Chuỗi đỏ là niềm tin sai, dẫn đến việc chờ hai dòng từ app cộng toàn bộ quầy. Chuỗi xanh là thực tế: LIMIT đứng giữa hai vế làm câu lệnh sai cú pháp, muốn giới hạn riêng một vế thì phải có ngoặc, còn ORDER BY và LIMIT cuối câu thuộc về cả kết quả.

Tự tay làm

Dùng chung hai danh sách app_customers và store_customers của bài.

Cơ bản

Bài 1. Dự đoán số dòng. Đầu vào: ba truy vấn đếm dưới đây. Điền số dòng mỗi truy vấn trả về trước khi chạy.

SELECT count(*) AS a FROM (SELECT email, phone FROM app_customers UNION ALL SELECT email, phone FROM store_customers) AS t;
SELECT count(*) AS b FROM (SELECT email, phone FROM app_customers UNION SELECT email, phone FROM store_customers) AS t;
SELECT count(*) AS c FROM (SELECT phone FROM app_customers UNION SELECT phone FROM store_customers) AS t;

Tiêu chí đạt: đúng cả ba số. Gợi ý: câu c chỉ còn một cột, hãy đếm các giá trị phone khác nhau, kể cả NULL.

Vận dụng

Bài 2. Báo cáo ba dòng. Đầu vào: hai danh sách của bài. Viết một truy vấn trả đúng 3 dòng gồm cột list và people: số dòng của app, số dòng của store và số email khác nhau của cả hai (nhãn distinct). Tiêu chí đạt: ba số 6, 5 và 6 theo đúng thứ tự. Gợi ý: nối ba SELECT count(*) bằng UNION ALL, vế thứ ba đếm trên một truy vấn con dùng UNION.

Thử thách (tùy chọn)

Bài 3. Mỗi người một dòng kèm danh sách nguồn. Đầu vào: hai danh sách của bài. Với mỗi email, in các nguồn mà email đó xuất hiện, ví dụ binh có app, store. Tiêu chí đạt: 6 dòng, binh và chi có hai nguồn. Gợi ý: nối hai vế có nhãn bằng UNION ALL (không loại trùng), rồi gom nhóm theo email với string_agg.

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.

1. (Mục tiêu 1, dự đoán) Hai danh sách của bài có 6 và 5 dòng. UNION ALL luôn cho mấy dòng, và UNION cho nhiều nhất là mấy dòng?
2. (Mục tiêu 2, giải thích) Vì sao thêm cột nhãn nguồn vào cả hai vế làm UNION giữ lại nhiều dòng hơn?
3. (Mục tiêu 2, so sánh) NULL = NULL trong WHERE cho kết quả gì, còn trong bước loại trùng của UNION hai NULL được xử lý ra sao?
4. (Mục tiêu 3, tìm lỗi) Câu SELECT email FROM a UNION SELECT 1 báo lỗi gì, mã bao nhiêu, và sửa thế nào?
5. (Mục tiêu 4, đặt đúng chỗ) Muốn lấy 2 email đầu của riêng danh sách app rồi nối với toàn bộ danh sách quầy, bạn viết thế nào?

Điều cần nhớ

• UNION ALL nối mọi dòng; UNION nối rồi bỏ các dòng giống hệt, nên chậm hơn vì có thêm bước loại trùng.
• Loại trùng so sánh nguyên dòng, và coi hai giá trị NULL là giống nhau.
• Cột ghép theo vị trí; lệch số cột báo 42601, lệch kiểu báo 42804; tên cột lấy từ vế đầu.
• ORDER BY và LIMIT cuối câu áp dụng cho cả kết quả, muốn riêng từng vế phải có ngoặc.

Cheat sheet:

Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.

Câu hỏi để dành cho ngày mai: UNION cho Chú Tư mọi khách có trong ít nhất một danh sách. Nếu Chú chỉ muốn những khách có mặt ở cả hai danh sách, hoặc chỉ ở danh sách này mà không ở danh sách kia, thì cần toán tử nào?

Đào sâu

Phần này dành cho bạn tò mò.

• Cơ chế bên trong. Với UNION, PostgreSQL có hai cách loại trùng: băm (HashAggregate) hoặc sắp xếp rồi bỏ dòng liền kề giống nhau (Sort kèm Unique). Cách thứ hai bắt buộc phải sắp xếp được, cách thứ nhất cần bộ nhớ chứa bảng băm. Vì vậy các kiểu không có phép so sánh bằng không dùng được với UNION. Ví dụ json [Đã chạy: PostgreSQL 16.15]:

SELECT '{"a": 1}'::json
UNION
SELECT '{"a": 1}'::json;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 42883
ERROR:  could not identify an equality operator for type json
LINE 1: SELECT '{"a": 1}'::json
               ^

Đổi sang jsonb thì chạy được [Đã chạy: PostgreSQL 16.15]:

SELECT '{"a": 1}'::jsonb
UNION
SELECT '{"a": 1}'::jsonb;

  jsonb   
----------
 {"a": 1}
(1 row)

• So sánh với lựa chọn khác. Thứ tự kết hợp là từ trái sang phải khi chỉ có UNION và UNION ALL [Đã chạy: PostgreSQL 16.15]:

SELECT n FROM (VALUES (1), (1)) AS a(n)
UNION ALL
SELECT n FROM (VALUES (1), (1)) AS b(n)
UNION
SELECT n FROM (VALUES (1)) AS c(n);

SELECT n FROM (VALUES (1), (1)) AS a(n)
UNION
SELECT n FROM (VALUES (1), (1)) AS b(n)
UNION ALL
SELECT n FROM (VALUES (1)) AS c(n);

 n 
---
 1
(1 row)

 n 
---
 1
 1
(2 rows)

Câu đầu trả 1 dòng vì UNION cuối cùng gộp tất cả. Câu thứ hai trả 2 dòng: UNION gộp hai vế đầu thành 1 dòng rồi UNION ALL cộng thêm 1 dòng nữa. Muốn chắc chắn thứ tự, hãy dùng ngoặc.

• Hiểu lầm nâng cao. Hằng số không có kiểu rõ (ví dụ 'abc') lấy kiểu của vế còn lại, nên lỗi lúc này đổi mã [Đã chạy: PostgreSQL 16.15]:

SELECT 'abc' AS x
UNION
SELECT 1;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 22P02
ERROR:  invalid input syntax for type integer: "abc"
LINE 1: SELECT 'abc' AS x
               ^

Mã 22P02 (sai dữ liệu) chứ không phải 42804, vì PostgreSQL đã cố đổi 'abc' thành integer. Cột text thật thì mới báo 42804.

• Giới hạn của bài. Bài này chưa nói đến INTERSECT, EXCEPT và thứ tự ưu tiên giữa các toán tử tập hợp. Nhớ rằng MySQL cũng có UNION với cú pháp tương tự, nhưng mã lỗi và thông báo lỗi khác PostgreSQL.
• Đọc thêm. Tài liệu PostgreSQL 16, mục Combining Queries: https://www.postgresql.org/docs/16/queries-union.html và mục UNION, CASE and Related Constructs: https://www.postgresql.org/docs/16/typeconv-union-case.html

Bài tiếp theo

Bài sau là INTERSECT: chỉ giữ những dòng có mặt ở cả hai vế. Mini-dự án nối tiếp: tìm khách vừa đăng ký qua app vừa ghi tên ở quầy trong database shop, và xem INTERSECT ALL đếm số lần lặp thế nào.

Lời giải và đáp án

Làm cùng mình, Bước 2. Ba chỗ trống điền như sau:

SELECT email
FROM app_customers
UNION
SELECT email
FROM store_customers
ORDER BY email
LIMIT 4;

     email     
---------------
 an@mail.com
 binh@mail.com
 chi@mail.com
 dung@mail.com
(4 rows)

UNION thay cho ... thứ nhất để mỗi người một lần. ORDER BY email và LIMIT 4 nằm sau vế cuối nên áp dụng cho cả kết quả: 4 email đầu theo chữ cái là an, binh, chi, dung.

Làm cùng mình, Bước 4. Thêm nhãn nguồn:

SELECT email, 'app' AS source
FROM app_customers
UNION
SELECT email, 'store'
FROM store_customers;

     email     | source 
---------------+--------
 an@mail.com   | app
 dung@mail.com | app
 chi@mail.com  | store
 binh@mail.com | store
 chi@mail.com  | app
 binh@mail.com | app
 gia@mail.com  | store
 em@mail.com   | store
(8 rows)

Kết quả này vẫn có binh hai lần và chi hai lần, vì một lần mang nhãn app và một lần mang nhãn store. Số dòng:

SELECT count(*) AS rows_with_source
FROM (
  SELECT email, 'app' AS source FROM app_customers
  UNION
  SELECT email, 'store' FROM store_customers
) AS t;

 rows_with_source 
------------------
                8
(1 row)

8 dòng, không phải 6. Nhãn nguồn làm hai dòng của cùng một người trở thành hai dòng khác nhau, nên UNION không gộp được nữa. Đây là lý do "mỗi người một tin" không còn đúng khi thêm nhãn.

Bài 1. Ba kết quả:

SELECT
  (SELECT count(*) FROM (SELECT email, phone FROM app_customers UNION ALL SELECT email, phone FROM store_customers) AS t) AS a,
  (SELECT count(*) FROM (SELECT email, phone FROM app_customers UNION SELECT email, phone FROM store_customers) AS t) AS b,
  (SELECT count(*) FROM (SELECT phone FROM app_customers UNION SELECT phone FROM store_customers) AS t) AS c;

 a  | b | c 
----+---+---
 11 | 6 | 4
(1 row)

Câu a: 11 dòng (6 cộng 5). Câu b: 6 dòng, vì cặp (email, phone) lặp được gộp. Câu c: 4 dòng, gồm 0901, 0903, 0905 và NULL.

Bài 2. Ba dòng báo cáo:

SELECT 'app' AS list, count(*) AS people FROM app_customers
UNION ALL
SELECT 'store', count(*) FROM store_customers
UNION ALL
SELECT 'distinct', count(*)
FROM (
  SELECT email FROM app_customers
  UNION
  SELECT email FROM store_customers
) AS t;

   list   | people 
----------+--------
 app      |      6
 store    |      5
 distinct |      6
(3 rows)

Bài 3. Danh sách nguồn:

SELECT email, string_agg(DISTINCT source, ', ' ORDER BY source) AS sources
FROM (
  SELECT email, 'app' AS source FROM app_customers
  UNION ALL
  SELECT email, 'store' FROM store_customers
) AS t
GROUP BY email
ORDER BY email;

     email     |  sources   
---------------+------------
 an@mail.com   | app
 binh@mail.com | app, store
 chi@mail.com  | app, store
 dung@mail.com | app
 em@mail.com   | store
 gia@mail.com  | store
(6 rows)

Tự kiểm tra.

1. UNION ALL luôn cho 11 dòng (6 cộng 5). UNION cho nhiều nhất 11 dòng, trường hợp không có dòng nào giống nhau; ở dữ liệu của bài là 6.
2. Vì nhãn nguồn thành một phần của dòng, nên hai dòng cùng email nhưng khác nhãn là hai dòng khác nhau và cùng ở lại.
3. Trong WHERE, NULL = NULL cho NULL nên dòng bị loại. Trong loại trùng của UNION, hai NULL được coi là giống nhau và gộp thành một.
4. Lỗi UNION types text and integer cannot be matched, mã 42804. Sửa bằng cách đưa hai vế về cùng kiểu, ví dụ SELECT 1::text, hoặc bỏ vế không cùng kiểu.
5. (SELECT email FROM app_customers ORDER BY email LIMIT 2) UNION ALL SELECT email FROM store_customers. Ngoặc quanh vế thứ nhất làm LIMIT 2 chỉ áp dụng cho vế đó.