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 set operations, but other clauses are not allowed.

The number of columns and the number of corresponding expressions in the result sets of each query participating in a set operation 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:

• When you query two CHAR values of the same length, the returned value is of the CHAR type with the same length. When you query CHAR values of different lengths, the returned value is of the VARCHAR2 type, with a length equal to the length of the longer CHAR value.

• When one or both 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 you query a BINARY_DOUBLE value, the returned value is of the BINARY_DOUBLE type.

• When no query selects a BINARY_DOUBLE value, 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.

OceanBase Database does not perform implicit type conversion across data type groups in queries that use set operators. If an expression in a component query 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 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 so that they can be referenced in the ORDER BY clause.

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

• You cannot specify an ORDER BY statement 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 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. 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 operations cannot specify the ALL attribute (they only have the DISTINCT attribute). By default, all set operations return distinct values, which is equivalent to using the UNION operator with the DISTINCT attribute.

Syntax

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

INTERSECT operator

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

Syntax

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

Limitations

• The number of columns and their order 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 (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 INTERSECT operator cannot be used with the COMPUTE or COMPUTE BY clauses.

• 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 that are returned by the query on the left side but not by the query on the right side.

Syntax

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

Execution order

When used with other operators in an expression, the MINUS operator is executed in the following order:

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,'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,'Fords');
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 | Fords     |
|    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 | Fords     |
|    2 | Patrol    |
|    3 | Taxi      |
|    4 | Lincoln   |
|    5 | New York  |
|    6 | Washington|
|    7 | Dell      |
|   10 | Lucent    |
|    1 | Fords     |
|    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 | Fords     |
|    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