Composite queries

You can use the set operators UNION, UNION ALL, INTERSECT, and MINUS to combine multiple queries into a composite query. All set operators have the same precedence. If a SQL statement contains multiple set operators, OceanBase Database evaluates them from left to right unless parentheses are used to specify the order.

Rules and limitations of set operators

In OceanBase Database, you can specify the ORDER BY and LIMIT clauses in set operations, but other clauses are not allowed.

The number of columns and the number of corresponding expressions in the query results of each participating query must be the same, and the data types must be compatible (for example, numeric or character data types).

Rules of set operators

If the composite query selects character data, the data type of the returned value is as follows:

• When querying two CHAR values of the same length, the returned value is of the same length as the CHAR values. When querying CHAR values of different lengths, the returned value is of the VARCHAR2 type, and the length is the length of the longer CHAR value.

• When one or both of the queries select VARCHAR2 values, the returned value is of the VARCHAR2 type.

If the composite query selects numeric data, the data type of the returned value is determined by the numeric precedence:

• When querying BINARY_DOUBLE values, the returned value is of the BINARY_DOUBLE type.

• When no query selects BINARY_DOUBLE values, but one or more queries select BINARY_FLOAT values, the returned value is of the BINARY_FLOAT type.

• When all queries select NUMBER values, the returned value is of the NUMBER type.

In queries that use set operators, OceanBase Database does not perform implicit type conversion across data types. If a component query's corresponding expression resolves to both character and numeric data, OceanBase Database returns an error.

Limitations of set operators

Set operators are subject to the following limitations:

• Set operators are not applicable to columns of the BLOB or CLOB type.

• If the SELECT list preceding a set operator contains expressions, column aliases must be provided for the expressions to be referenced in the ORDER BY clause.

• You cannot use the set operator to specify the UPDATE statement.

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

• These operators cannot be used in SELECT statements that contain table set expressions.

UNION and UNION ALL operators

The UNION operator is used to combine the result sets of two or more SELECT statements. The SELECT statements within the UNION operator must have the same number of columns, and the columns must have compatible data types. Additionally, the order of columns in each SELECT statement must be the same. You can specify the UNION operator as UNION ALL or UNION DISTINCT (or simply UNIQUE) to indicate whether the set values are allowed to be repeated or not. Other set operations cannot specify the ALL attribute; they only have the DISTINCT attribute. By default, all set operations use the DISTINCT attribute, meaning that the UNION operator selects only unique values.

Syntax

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

INTERSECT operator

The INTERSECT operator returns the intersection of two result sets, which consists of all unique values returned by both queries.

Syntax

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

Limitations

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

• The data types of the columns in the two result sets being compared can be different but must be compatible.

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

• The column names in the result set returned by the INTERSECT operator are the same as the column names returned by the query on the left side of the operator. The column names or aliases in the ORDER BY clause must reference the column names returned by the left query.

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

• When determining unique values by comparing rows, two NULL values are considered equal.

MINUS operator

The MINUS operator returns the difference between two result sets, which consists of all unique values returned by the left query that are not returned by the right query.

Syntax

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

Execution order

When used with other operators in an expression, the execution order of the MINUS operator is as follows:

1. Evaluate the expressions within the parentheses.

2. Execute the INTERSECT operator.

3. Execute the MINUS and UNION operators from left to right based on their position in the expression.

If the MINUS or INTERSECT operator is used to compare more than two query sets, data type conversion is determined by comparing the queries in pairs and follows the expression evaluation rules mentioned earlier.

Examples

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,'Fords');
INSERT INTO table_a VALUES(2,'Police car');
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,'Fords');
INSERT INTO table_b VALUES(2,'Police car');
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');

UNION example

obclient> SELECT PK, name FROM table_a
      UNION
      SELECT PK, name FROM table_b;
+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | Fords     |
|    2 | Police car|
|    3 | Taxi      |
|    4 | Lincoln   |
|    5 | New York  |
|    6 | Washington|
|    7 | Dell      |
|   10 | Lucent    |
|    8 | Microsoft |
|    9 | Apple     |
|   11 | Scotland  |
+------+-----------+
11 rows in set

UNION ALL example

obclient> SELECT PK, name FROM table_a
      UNION ALL
      SELECT PK, name FROM table_b;
+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | Fords     |
|    2 | Police car|
|    3 | Taxi      |
|    4 | Lincoln   |
|    5 | New York  |
|    6 | Washington|
|    7 | Dell      |
|   10 | Lucent    |
|    1 | Fords     |
|    2 | Police car|
|    3 | Taxi      |
|    6 | Washington|
|    7 | Dell      |
|    8 | Microsoft |
|    9 | Apple     |
|   11 | Scotland  |
+------+-----------+
16 rows in set

INTERSECT example

obclient> SELECT PK, NAME FROM table_a
      INTERSECT
      SELECT PK, NAME FROM table_b;
+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | Fords     |
|    2 | Police car|
|    3 | Taxi      |
|    6 | Washington|
|    7 | Dell      |
+------+-----------+
5 rows in set

MINUS example

obclient> SELECT PK, NAME FROM table_a
      MINUS
      SELECT PK, NAME FROM table_b;
+------+--------+
| PK   | NAME   |
+------+--------+
|    4 | Lincoln|
|    5 | New York|
|   10 | Lucent |
+------+--------+
3 rows in set