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 for set operators

In OceanBase Database, you can specify the ORDER BY and LIMIT clauses in a set operation, 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 for set operators

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

• If the query selects two CHAR values of the same length, the returned value is a CHAR value of the same length. If the query selects CHAR values of different lengths, the returned value is a VARCHAR2 value with the length of the longer CHAR value.

• If one or both of the queries select VARCHAR2 values, the returned value is a VARCHAR2 value.

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

• If the query selects a BINARY_DOUBLE value, the returned value is a BINARY_DOUBLE value.

• If no query selects a BINARY_DOUBLE value, but one or more queries select a BINARY_FLOAT value, the returned value is a BINARY_FLOAT value.

• If all queries select NUMBER values, the returned value is a NUMBER value.

OceanBase Database does not perform implicit conversions between different data types in a set operation. If a component query's corresponding expression resolves to both character data and numeric data, OceanBase Database returns an error.

Limitations for set operators

Set operators are subject to the following limitations:

• Set operators are not supported for BLOB and CLOB columns.

• If the SELECT list preceding a set operator contains expressions, you must provide column aliases for the expressions to reference them in the ORDER BY clause.

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

• You cannot specify an ORDER BY statement in a subquery of a set operator.

• You cannot use these operators in a SELECT statement that contains a table set expression.

UNION and UNION ALL operators

The UNION operator combines 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 duplicate values are allowed in the result set. Other set operators cannot specify the ALL option; they only support the DISTINCT option. By default, all set operators return distinct 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 non-duplicate values returned by both queries.

Syntax

{ (< SQL- query statement 1>) }
INTERSECT
{ (< SQL- query statement 2>) }

Limitations

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

• The data types of corresponding columns in the result sets of the two queries must be compatible, even if they are different.

• The result sets of the two queries cannot contain columns of incomparable data types, 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 those returned by the left query. The column names or aliases in the ORDER BY clause must reference columns returned by the left query.

• You cannot use the COMPUTE or COMPUTE BY clauses with the INTERSECT operator.

• When determining non-duplicate 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 non-duplicate values returned by the left query but not 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 MINUS operator is evaluated in the following order:

1. Evaluate the expressions within parentheses.

2. Execute the INTERSECT operator.

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

If MINUS or INTERSECT is used to compare more than two query sets, data type conversions are determined by comparing pairs of queries and follow 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