OceanBase 集合

您可以使用集合運算符 UNION、UNION ALL、INTERSECT 和 MINUS 來組合多個查詢。 所有集合運算符都具有相同的優(yōu)先級。如果 SQL 語句包含多個集合運算符，則 OceanBase 從左到右對它們進行判斷，除非括號中指定了順序。

集合運算符的規(guī)則和限制

您可以指定 UNION 的屬性為 ALL 和 DISTINCT 或 UNIQUE。分別代表集合可重復，和集合不可重復。而其它幾種集合操作是不能指定 ALL 屬性的（它們只有 DISTINCT 屬性）。所有的集合操作默認的屬性是 DISTINCT。在 Oceanbase 中，集合操作中可以指定 ORDER BY 和 LIMIT 子句，但是不允許其他子句的出現(xiàn)。我們僅支持MINUS，不支持 EXCEPT 盡管這兩者的語義是相同的。參加集合操作的各查詢結果的列數(shù)和相應表達式的數(shù)量必須相同，對應的數(shù)據(jù)類型也必須兼容（例如數(shù)值或字符）。

集合運算符規(guī)則

如果組件查詢是字符數(shù)據(jù)，則返回值的數(shù)據(jù)類型如下：

• 查詢兩個長度相等的 VARCHAR2 類型值，則返回的值為相同長度的 CHAR 類型。 查詢不同長度的 CHAR 類型值，則返回的值為 VARCHAR2 類型，其長度為較大的 CHAR 值。

• 其中一個或兩個查詢是 VARCHAR2 數(shù)據(jù)類型的值，則返回的值為 VARCHAR2 類型。

如果組件查詢是數(shù)值數(shù)據(jù)，則返回值的數(shù)據(jù)類型由數(shù)值優(yōu)先級決定：

• 查詢 BINARY_DOUBLE 類型值，則返回的值為 BINARY_DOUBLE 類型。

• 查詢都選擇 BINARY_FLOAT 類型值，則返回的值為 BINARY_FLOAT 類型。

• 所有查詢都選擇 NUMBER 類型值，則返回的值為 NUMBER 類型。

在使用集合運算符的查詢中，OceanBase 不會跨數(shù)據(jù)類型組執(zhí)行隱式轉換。如果組件查詢的相應表達式同時解析為字符數(shù)據(jù)和數(shù)值數(shù)據(jù)，OceanBase 將返回錯誤。

集合運算符限制

集合運算符受到以下限制：

• 集合運算符在 BLOB 和 CLOB 的列上無效。

• 如果集合運算符前面的 SELECT 列表包含表達式，則必須為表達式提供列別名，以便在 ORDER BY 子句引用。

• 您不能用設置的運算符指定 UPDATE 語句。

• 您不能在這些運算符的子查詢中指定 ORDER BY 語句。

• 不能在包含表集合表達式的 SELECT 語句中使用這些運算符 。

UNION 運算符和 UNION ALL 運算符

UNION 運算符用于合并兩個或多個 SELECT 語句的結果集。UNION 內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時，每條 SELECT 語句中的列的順序必須相同。默認地，UNION 運算符選取不同的值。如果允許重復的值，請使用 UNION ALL。

語法

{ (< SQL- 查詢語句 1>) }
UNION [ALL]
{ (< SQL- 查詢語句 2>) }

INTERSECT 運算符

返回兩個結果集的交集，即兩個查詢都返回的所有非重復值。

語法

{ (< SQL- 查詢語句 1>) }
INTERSECT
{ (< SQL- 查詢語句 2>) }

限制條件

• 所有查詢中的列數(shù)和列的順序必須相同。

• 比較的兩個查詢結果集中的列數(shù)據(jù)類型可以不同但必須兼容。

• 比較的兩個查詢結果集中不能包含不可比較的數(shù)據(jù)類型列（XML、TEXT、NTEXT、IMAGE 或非二進制 CLR 用戶定義類型）。

• 返回的結果集的列名與操作數(shù)左側的查詢返回的列名相同。ORDER BY 子句中的列名或別名必須引用左側查詢返回的列名。

• 不能與 COMPUTE 和 COMPUTE BY 子句一起使用。

• 通過比較行來確定非重復值時，兩個 NULL 值被視為相等。

MINUS 運算符

MINUS 運算符返回兩個結果集的差，即從左查詢中返回右查詢沒有找到的所有非重復值。

語法

{ (< SQL- 查詢語句 1>) }
MINUS
{ (< SQL- 查詢語句 2>) }

執(zhí)行順序

與表達式中的其他運算符一起使用時的執(zhí)行順序:

１、括號中的表達式。

２、INTERSECT 操作數(shù)。

３、基于在表達式中的位置從左到右求值的 MINUS 和 UNION。

如果 MINUS 或 INTERSECT 用于比較兩個以上的查詢集，則數(shù)據(jù)類型轉換是通過一次比較兩個查詢來確定的，并遵循前面提到的表達式求值規(guī)則。

示例

以下語句創(chuàng)建了表 table_a 和表 table_b，并向表中插入數(shù)據(jù)：

CREATE TABLE table_a(PK INT, name VARCHAR(25));
INSERT INTO table_a VALUES(1,'福克斯');
INSERT INTO table_a VALUES(2,'警察');  
INSERT INTO table_a VALUES(3,'的士');  
INSERT INTO table_a VALUES(4,'林肯');  
INSERT INTO table_a VALUES(5,'紐約');  
INSERT INTO table_a VALUES(6,'華盛頓');  
INSERT INTO table_a VALUES(7,'戴爾');  
INSERT INTO table_a VALUES(10,'朗訊'); 
CREATE TABLE table_b(PK INT, name VARCHAR(25));
INSERT INTO table_b VALUES(1,'?？怂?#39;);
INSERT INTO table_b VALUES(2,'警察');  
INSERT INTO table_b VALUES(3,'的士');  
INSERT INTO table_b VALUES(6,'華盛頓');  
INSERT INTO table_b VALUES(7,'戴爾');  
INSERT INTO table_b VALUES(8,'微軟');  
INSERT INTO table_b VALUES(9,'蘋果'); 
INSERT INTO table_b VALUES(11,'蘇格蘭');

UNION 示例：

SELECT PK, name FROM table_a
UNION
SELECT PK, name FROM table_b;

查詢結果如下：

+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | 福克斯    |
|    2 | 警察      |
|    3 | 的士      |
|    4 | 林肯      |
|    5 | 紐約      |
|    6 | 華盛頓    |
|    7 | 戴爾      |
|   10 | 朗訊      |
|    8 | 微軟      |
|    9 | 蘋果      |
|   11 | 蘇格蘭    |
+------+-----------+

UNION ALL 示例：

SELECT PK, name FROM table_a
UNION ALL
SELECT PK, name FROM table_b;

查詢結果如下：

+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | ?？怂?   |
|    2 | 警察      |
|    3 | 的士      |
|    4 | 林肯      |
|    5 | 紐約      |
|    6 | 華盛頓    |
|    7 | 戴爾      |
|   10 | 朗訊      |
|    1 | 福克斯    |
|    2 | 警察      |
|    3 | 的士      |
|    6 | 華盛頓    |
|    7 | 戴爾      |
|    8 | 微軟      |
|    9 | 蘋果      |
|   11 | 蘇格蘭    |
+------+-----------+

INTERSECT 示例：

SELECT PK, NAME FROM table_a  
INTERSECT
SELECT PK, NAME FROM table_b;

查詢結果如下：

+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | 福克斯    |
|    2 | 警察      |
|    3 | 的士      |
|    6 | 華盛頓    |
|    7 | 戴爾      |
+------+-----------+

MINUS 示例：

SELECT PK, NAME FROM table_a 
MINUS 
SELECT PK, NAME FROM table_b;

查詢結果如下：

+------+--------+
| PK   | NAME   |
+------+--------+
|    4 | 林肯   |
|    5 | 紐約   |
|   10 | 朗訊   |
+------+--------+