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. The corresponding data types must also 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 you query two CHAR values of the same length, the returned value is a CHAR value of the same length. If you query CHAR values of different lengths, the returned value is a VARCHAR2 value of the length of the longer CHAR value.

• If one or both 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 you query 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 BINARY_FLOAT values, 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 type conversion across data type groups in queries that use set operators. If a component query's corresponding expression is resolved 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 invalid for BLOB and CLOB columns.

• If the SELECT list preceding a set operator contains expressions, you must provide column aliases for the expressions so that they can be referenced in the ORDER BY clause.

• You cannot use the set operator in an UPDATE statement.

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

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

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. The columns must also 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 attribute; they only have the DISTINCT attribute. By default, all set operators return distinct values, which is equivalent to using the UNION operator with the DISTINCT attribute.

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-repeating values that are 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 result sets of the two queries can be different but must be compatible.

• 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 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 query on the left side.

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

• When determining non-repeating 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-repeating values that are 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 executed in the following order:

1. Evaluate the expressions in 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 query sets two at a time, following 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,'Foxy');
INSERT INTO table_a VALUES(2,'Patrol');
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,'Foxy');
INSERT INTO table_b VALUES(2,'Patrol');
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 | Foxy      |
|    2 | Patrol    |
|    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 | Foxy      |
|    2 | Patrol    |
|    3 | Taxi      |
|    4 | Lincoln   |
|    5 | New York  |
|    6 | Washington|
|    7 | Dell      |
|   10 | Lucent    |
|    1 | Foxy      |
|    2 | Patrol    |
|    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 | Foxy      |
|    2 | Patrol    |
|    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