Sets

You can use the following set operators to combine multiple queries: UNION, UNION ALL, INTERSECT, and MINUS. All the set operators have equal precedence. If an SQL statement contains multiple set operators, ApsaraDB for OceanBase checks the operators in the left-to-right order unless an order is specified in the parentheses.

Rules and limits on set operators

You can specify the attribute of UNION as ALL and DISTINCT or UNIQUE. ALL indicates that duplicate values are allowed in a set. DISTINCT or UNIQUE indicates that duplicate values are not allowed in a set. However, you cannot specify the ALL attribute for other types of set operations. They have only the DISTINCT attribute. The default attribute of all set operations is DISTINCT. In ApsaraDB for OceanBase, you can specify ORDER BY and LIMIT clauses in set operations. However, other clauses are not allowed. Only MINUS is supported and EXCEPT is not supported, although both of them have the same semantics. The number of columns in each query result in a set operation must be the same as that of the corresponding expressions. The corresponding data types must be compatible, such as numeric or character.

Rules of set operators

If component queries query character data, the return values are of the following data types:

• If two values of the VARCHAR2 type in equal length are queried, the return value is of the CHAR type in the same length. If the values of the CHAR type in different length are queried, the return value is of the VARCHAR2 type. Its length is same as that of a larger CHAR value.

• If one or two of the queries query the values of the VARCHAR2 data type, the return value is of the VARCHAR2 type.

If component queries query numeric data, the data types of the return values are determined by numeric precedence.

• If the values of the BINARY_DOUBLE type are queried, the return value is of the BINARY_DOUBLE type.

• If the queries select values of the BINARY_FLOAT type, the return value is of the BINARY_FLOAT type.

• If all the queries select values of the NUMBER type, the return value is of the NUMBER type.

In queries that use set operators, ApsaraDB for OceanBase does not perform implicit conversion across data type groups. If the corresponding expressions of component queries are resolved into character data and numeric data at the same time, ApsaraDB for OceanBase returns an error.

Limits on set operators

Set operators are subject to the following limits:

• Set operators are invalid on columns of BLOB and CLOB.

• If the SELECT list before the set operator contains an expression, you must provide a column alias for the expression. Then, you can reference the column alias in the ORDER BY clause.

• You cannot use set operators to specify UPDATE statements.

• You cannot specify ORDER BY statements in the subqueries of these operators.

• You cannot use these operators in the SELECT statements that contain table collection expressions.

UNION and UNION ALL operators

The UNION operator combines the result sets of two or more SELECT statements. The SELECT statements inside UNION must have the same number of columns. The data types of the columns must be similar. In addition, the order of columns in each SELECT statement must be the same. By default, the UNION operator selects different values. To allow duplicate values, use UNION ALL.

Syntax

{ (< SQL- Statement 1>) }
UNION [ALL]
{ (< SQL- Statement 2>) }

INTERSECT operator

The INTERSECT operator returns the intersection of two result sets, that is, all the distinct values that are returned by both queries.

Syntax

{ (< SQL- Statement 1>) }
INTERSECT
{ (< SQL- Statement 2>) }

Limits

• The number and order of the columns in all the queries must be same.

• The column data types in the two query result sets that are compared can be different but must be compatible.

• The two query result sets that are compared cannot contain columns of incomparable data types, such as XML, TEXT, NTEXT, IMAGE, or non-binary CLR user-defined types.

• The column names of the returned result set are the same as those returned by the query to the left of the operand. The column names or aliases in ORDER BY clauses must reference the column names that are returned by the left-side query.

• The INTERSECT operator cannot be used together with COMPUTE and COMPUTE BY clauses.

• When rows are compared to determine distinct values, two NULL values are considered equal.

MINUS operator

The MINUS operator returns the difference between two result sets, that is, all the distinct values that are returned by the left-side query but not retrieved by the right-side query.

Syntax

{ (< SQL- Statement 1>) }
MINUS
{ (< SQL- Statement 2>) }

Execution sequence

The following execution sequence is applied when the MINUS operator is used together with other operators in expressions:

1. Expressions in parentheses

2. The INTERSECT operand

3. MINUS and UNION that are evaluated from left to right based on their positions in the expressions

If MINUS or INTERSECT is used to compare more than two query result sets, the data types are converted based on one comparison between two queries. The preceding rules for evaluating expressions are followed.

Examples

The following statements create tables table_a and table_b and insert data into the tables:

CREATE TABLE table_a(PK INT, name VARCHAR(25));
INSERT INTO table_a VALUES(1,'Fox');
INSERT INTO table_a VALUES(2,'Police');  
INSERT INTO table_a VALUES(3,'Taxi');  
INSERT INTO table_a VALUES(4,'Lincoln');  
INSERT INTO table_a VALUES(5,'New York');  
INSERT INTO table_a VALUES(6,'Washington');  
INSERT INTO table_a VALUES(7,'Dell');  
INSERT INTO table_a VALUES(10,'Lucent'); 
CREATE TABLE table_b(PK INT, name VARCHAR(25));
INSERT INTO table_b VALUES(1,'Fox');
INSERT INTO table_b VALUES(2,'Police');  
INSERT INTO table_b VALUES(3,'Taxi');  
INSERT INTO table_b VALUES(6,'Washington');  
INSERT INTO table_b VALUES(7,'Dell');  
INSERT INTO table_b VALUES(8,'Microsoft');  
INSERT INTO table_b VALUES(9,'Apple'); 
INSERT INTO table_b VALUES(11,'Scotland');

Example of UNION :

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

The following query result is returned:

+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | Fox    |
|    2 | Police      |
|    3 | Taxi      |
|    4 | Lincoln      |
|    5 | New York      |
|    6 | Washington    |
|    7 | Dell      |
|   10 | Lucent      |
|    8 | Microsoft      |
|    9 | Apple      |
|   11 | Scotland    |
+------+-----------+

Example of UNION ALL :

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

The following query result is returned:

+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | Fox    |
|    2 | Police      |
|    3 | Taxi      |
|    4 | Lincoln      |
|    5 | New York      |
|    6 | Washington    |
|    7 | Dell      |
|   10 | Lucent      |
|    1 | Fox    |
|    2 | Police      |
|    3 | Taxi      |
|    6 | Washington    |
|    7 | Dell      |
|    8 | Microsoft      |
|    9 | Apple      |
|   11 | Scotland    |
+------+-----------+

Example of INTERSECT :

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

The following query result is returned:

+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | Fox    |
|    2 | Police      |
|    3 | Taxi      |
|    6 | Washington    |
|    7 | Dell      |
+------+-----------+

Example of MINUS :

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

The following query result is returned:

+------+--------+
| PK   | NAME   |
+------+--------+
|    4 | Lincoln   |
|    5 | New York   |
|   10 | Lucent   |
+------+--------+