OceanBase 集合

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

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

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

集合運(yùn)算符規(guī)則

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

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

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

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

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

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

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

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

集合運(yùn)算符限制

集合運(yùn)算符受到以下限制：

• 集合運(yùn)算符在 BLOB 和 CLOB 的列上無效。

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

• 您不能用設(shè)置的運(yùn)算符指定 UPDATE 語句。

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

• 不能在包含表集合表達(dá)式的 SELECT 語句中使用這些運(yùn)算符 。

UNION 運(yùn)算符和 UNION ALL 運(yùn)算符

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

語法

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

INTERSECT 運(yùn)算符

返回兩個(gè)結(jié)果集的交集，即兩個(gè)查詢都返回的所有非重復(fù)值。

語法

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

限制條件

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

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

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

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

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

• 通過比較行來確定非重復(fù)值時(shí)，兩個(gè) NULL 值被視為相等。

MINUS 運(yùn)算符

MINUS 運(yùn)算符返回兩個(gè)結(jié)果集的差，即從左查詢中返回右查詢沒有找到的所有非重復(fù)值。

語法

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

執(zhí)行順序

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

１、括號(hào)中的表達(dá)式。

２、INTERSECT 操作數(shù)。

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

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

示例

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

CREATE TABLE table_a(PK INT, name VARCHAR(25));
INSERT INTO table_a VALUES(1,'?？怂?#39;);
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;

查詢結(jié)果如下：

+------+-----------+
| 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;

查詢結(jié)果如下：

+------+-----------+
| 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;

查詢結(jié)果如下：

+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | ?？怂?   |
|    2 | 警察      |
|    3 | 的士      |
|    6 | 華盛頓    |
|    7 | 戴爾      |
+------+-----------+

MINUS 示例：

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

查詢結(jié)果如下：

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