使用 Postgresql 展示资料库集合操作

-- 使用 Postgresql 展示资料库集合操作-- 在 .psqlrc 中设定 \pset null '¤'-- 将 NULL 显示为  ¤ , 以利观察create table it191210a (  id int generated always as identity, txt text );create table it191210b (  id int generated always as identity, txt text );create table it191210c (  id int generated always as identity, txt text );insert into it191210a (txt) values('小岛南'),('小岛南'),('小岛南'),('初川南'),('初川南'),('初川南'),('相沢南'),('明里つむぎ'),('明里つむぎ'),('七沢みあ'),(NULL);insert into it191210b (txt) values('小岛南'),('小岛南'),('相沢南'),('相沢南'),('相沢南'),('相沢南'),('明里つむぎ');-- it191210c 不输入资料.------ UNION [DISTINCT] select txt  from it191210aunionselect txt  from it191210b;+------------+|    txt     |+------------+| ¤          || 明里つむぎ || 初川南     || 小岛南     || 七沢みあ   || 相沢南     |+------------+(6 rows)-- 注意,包含了 NULL-- UNION ALLselect txt     , array_agg(q_id) query_and_id  from (select 'a_' || id::text as q_id             , txt          from it191210a         union all         select 'b_' || id::text as q_id              , txt           from it191210b) c group by txt;+------------+-----------------------+|    txt     |     query_and_id      |+------------+-----------------------+| ¤          | {a_11,b_8}            || 七沢みあ   | {a_10}                || 小岛南     | {a_1,a_2,a_3,b_1,b_2} || 明里つむぎ | {a_8,a_9,b_7}         || 相沢南     | {a_7,b_3,b_4,b_5,b_6} || 初川南     | {a_4,a_5,a_6}         |+------------+-----------------------+(6 rows)-- 为了不显示一长串,利用了 array 功能.----------------  EXCEPT [DISTINCT] -- query a except query bselect txt  from it191210aexceptselect txt  from it191210b;  +----------+|   txt    |+----------+| 七沢みあ || 初川南   |+----------+(2 rows)-- query b except query aselect txt  from it191210bexceptselect txt  from it191210a;+-----+| txt |+-----++-----+(0 rows)-- 相同的做 exceptselect txt  from it191210aexcept select txt  from it191210a;+-----+| txt |+-----++-----+(0 rows)----------------- EXCEPT ALL-- query a except all query bselect txt  from it191210aexcept allselect txt  from it191210b;+------------+|    txt     |+------------+| 七沢みあ   || 小岛南     || 明里つむぎ || 初川南     || 初川南     || 初川南     |+------------+(6 rows)-- query b except all query aselect txt  from it191210bexcept allselect txt  from it191210a;+--------+|  txt   |+--------+| 相沢南 || 相沢南 || 相沢南 |+--------+(3 rows)----------------- intersect [distinct]select txt  from it191210aintersectselect txt  from it191210b;+------------+|    txt     |+------------+| ¤          || 小岛南     || 明里つむぎ || 相沢南     |+------------+(4 rows)-- 注意到 包含了 NULL-- intersect allselect txt  from it191210aintersect allselect txt  from it191210b;+------------+|    txt     |+------------+| ¤          || 小岛南     || 小岛南     || 明里つむぎ || 相沢南     |+------------+(5 rows)---------------- 与空集合运算 -- query a union / except query c (没资料, 空集合)select txt  from it191210aunionselect txt  from it191210c;  +------------+|    txt     |+------------+| ¤          || 明里つむぎ || 初川南     || 小岛南     || 七沢みあ   || 相沢南     |+------------+(6 rows)select txt  from it191210aexceptselect txt  from it191210c;+------------+|    txt     |+------------+| ¤          || 七沢みあ   || 小岛南     || 明里つむぎ || 相沢南     || 初川南     |+------------+(6 rows)-- 相当于 query a 做 distinctselect distinct txt  from it191210a;+------------+|    txt     |+------------+| ¤          || 七沢みあ   || 小岛南     || 明里つむぎ || 相沢南     || 初川南     |+------------+(6 rows)-- all 的情况 就跟 query a 一样,可以自行验证.