数据库中的集合操作可以把多个查询的结果组合成一个结果集。

集合操作主要包含：

  • UNION

  • INTERSECT

  • EXCEPT/MINUS

注意

OceanBase 数据库同时支持 EXCEPT 与 MINUS，这两者的语义和语法都是相同的。

对于 UNION 来说用户可以指定 UNION 的属性为 ALL（集合可重复） 和 DISTINCT/UNIQUE（集合不可重复）。所有的集合操作默认的属性是 DISTINCT（OceanBase 数据库的 DISTINCT 和 UNIQUE 的语义语法是相同的），而其他的几种集合操作是不能指定属性的。

执行计划

所有的集合操作在逻辑计划层面的展示都是 [UNION|INTERSECT|EXCEPT] [ALL|DISTINCT] 的形式。带有 DISTINCT 关键字时，需要从输出的结果中去掉重复的行，目前 OceanBase 数据库只支持基于排序的集合 DISTINCT 去重，在计划层面，排序操作会下压到各分支内部进行。

  1. obclient> explain select * from t1 union select * from t1\G；
  2. *************************** 1. row ***************************
  3. Query Plan: 
  4. =======================================
  5. |ID|OPERATOR      |NAME|EST. ROWS|COST|
  6. ---------------------------------------
  7. |0 |UNION DISTINCT|    |2000     |4861|
  8. |1 | SORT         |    |1000     |2048|
  9. |2 |  TABLE SCAN  |t1  |1000     |498 |
  10. |3 | SORT         |    |1000     |2048|
  11. |4 |  TABLE SCAN  |t1  |1000     |498 |
  12. =======================================
  14. Outputs & filters:
  15. -------------------------------------
  16.   0 - output([UNION(t1.c1, t1.c1)], [UNION(t1.c2, t1.c2)]), filter(nil)
  17.   1 - output([t1.c1], [t1.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC])
  18.   2 - output([t1.c1], [t1.c2]), filter(nil),
  19.       access([t1.c1], [t1.c2]), partitions(p0)
  20.   3 - output([t1.c1], [t1.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC])
  21.   4 - output([t1.c1], [t1.c2]), filter(nil),
  22.       access([t1.c1], [t1.c2]), partitions(p0)
  24. 1 row in set (0.02 sec)
  26. obclient> explain select * from t1 intersect select * from t1\G；
  27. *************************** 1. row ***************************
  28. Query Plan: 
  29. ===========================================
  30. |ID|OPERATOR          |NAME|EST. ROWS|COST|
  31. -------------------------------------------
  32. |0 |INTERSECT DISTINCT|    |1000     |4723|
  33. |1 | SORT             |    |1000     |2048|
  34. |2 |  TABLE SCAN      |t1  |1000     |498 |
  35. |3 | SORT             |    |1000     |2048|
  36. |4 |  TABLE SCAN      |t1  |1000     |498 |
  37. ===========================================
  39. Outputs & filters:
  40. -------------------------------------
  41.   0 - output([INTERSECT(t1.c1, t1.c1)], [INTERSECT(t1.c2, t1.c2)]), filter(nil)
  42.   1 - output([t1.c1], [t1.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC])
  43.   2 - output([t1.c1], [t1.c2]), filter(nil),
  44.       access([t1.c1], [t1.c2]), partitions(p0)
  45.   3 - output([t1.c1], [t1.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC])
  46.   4 - output([t1.c1], [t1.c2]), filter(nil),
  47.       access([t1.c1], [t1.c2]), partitions(p0)
  49. 1 row in set (0.02 sec)
  51. obclient> explain select * from t1 minus select * from t1\G；
  52. *************************** 1. row ***************************
  53. Query Plan: 
  54. ========================================
  55. |ID|OPERATOR       |NAME|EST. ROWS|COST|
  56. ----------------------------------------
  57. |0 |EXCEPT DISTINCT|    |1000     |4723|
  58. |1 | SORT          |    |1000     |2048|
  59. |2 |  TABLE SCAN   |t1  |1000     |498 |
  60. |3 | SORT          |    |1000     |2048|
  61. |4 |  TABLE SCAN   |t1  |1000     |498 |
  62. ========================================
  64. Outputs & filters:
  65. -------------------------------------
  66.   0 - output([EXCEPT(t1.c1, t1.c1)], [EXCEPT(t1.c2, t1.c2)]), filter(nil)
  67.   1 - output([t1.c1], [t1.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC])
  68.   2 - output([t1.c1], [t1.c2]), filter(nil),
  69.       access([t1.c1], [t1.c2]), partitions(p0)
  70.   3 - output([t1.c1], [t1.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC])
  71.   4 - output([t1.c1], [t1.c2]), filter(nil),
  72.       access([t1.c1], [t1.c2]), partitions(p0)
  74. 1 row in set (0.01 sec)

如下例所示，带有 ALL 属性的集合操作不要求下层有序。

  1. obclient> explain select * from t1 union all select * from t1\G；
  2. *************************** 1. row ***************************
  3. Query Plan: 
  4. ====================================
  5. |ID|OPERATOR   |NAME|EST. ROWS|COST|
  6. ------------------------------------
  7. |0 |UNION ALL  |    |2000     |1761|
  8. |1 | TABLE SCAN|t1  |1000     |498 |
  9. |2 | TABLE SCAN|t1  |1000     |498 |
  10. ====================================
  12. Outputs & filters:
  13. -------------------------------------
  14.   0 - output([UNION(t1.c1, t1.c1)], [UNION(t1.c2, t1.c2)]), filter(nil)
  15.   1 - output([t1.c1], [t1.c2]), filter(nil),
  16.       access([t1.c1], [t1.c2]), partitions(p0)
  17.   2 - output([t1.c1], [t1.c2]), filter(nil),
  18.       access([t1.c1], [t1.c2]), partitions(p0)
  20. 1 row in set (0.02 sec)

当集合操作两边出现类型不一致的时候，SQL 层会为 UNION 左右两边的表达式分配 CAST 转换，这个操作在 EXPLAIN 中是可见的。

  1. obclient> create table t1 (c1 int, c2 varchar(5));
  2. Query OK, 0 rows affected (0.12 sec)
  4. obclient> explain select c1 from t1 union select c2 from t1\G；
  5. *************************** 1. row ***************************
  6. Query Plan: 
  7. ========================================
  8. |ID|OPERATOR      |NAME|EST. ROWS|COST |
  9. ----------------------------------------
  10. |0 |UNION DISTINCT|    |2000     |11986|
  11. |1 | SORT         |    |1000     |5664 |
  12. |2 |  TABLE SCAN  |t1  |1000     |476  |
  13. |3 | SORT         |    |1000     |5664 |
  14. |4 |  TABLE SCAN  |t1  |1000     |476  |
  15. ========================================
  17. Outputs & filters:
  18. -------------------------------------
  19.   0 - output([UNION(cast(t1.c1, VARCHAR(11)), cast(t1.c2, VARCHAR(11)))]), filter(nil)
  20.   1 - output([cast(t1.c1, VARCHAR(11))]), filter(nil), sort_keys([cast(t1.c1, VARCHAR(11)), ASC])
  21.   2 - output([cast(t1.c1, VARCHAR(11))]), filter(nil),
  22.       access([t1.c1]), partitions(p0)
  23.   3 - output([cast(t1.c2, VARCHAR(11))]), filter(nil), sort_keys([cast(t1.c2, VARCHAR(11)), ASC])
  24.   4 - output([cast(t1.c2, VARCHAR(11))]), filter(nil),
  25.       access([t1.c2]), partitions(p0)
  27. 1 row in set (0.01 sec)