Compound query|V4.1.0| docs|Distributed Database

Compound query

Last Updated：2023-07-28 02:55:42 Updated

You can use set operators such as UNION, UNION ALL, INTERSECT, and MINUS to combine multiple queries to form a compound query. All set operators have the same precedence. If an SQL statement contains multiple set operators, OceanBase Database evaluates them from left to right unless otherwise forced by parentheses.

Rules and limitations for set operators

OceanBase Database allows you to specify ORDER BY and LIMIT clauses in set operations, but you cannot specify other clauses.

The corresponding columns and expressions in the select lists of compound queries in a compound query must match in number and must be in the same data type group (such as numeric or character).

Rules for set operators

If component queries select data of a character data type, the data type of the return values is determined as follows:

If both queries select CHAR values of equal length, CHAR values of the same length are returned. If the queries select CHAR values of different lengths, VARCHAR2 values with the length of the greater CHAR value are returned.
If either or both queries select VARCHAR2 values, VARCHAR2 values are returned.

If component queries select data of a numeric data type, the data type of the return values is determined based on numeric precedence as follows:

If any query selects BINARY_DOUBLE values, BINARY_DOUBLE values are returned.
If no query selects BINARY_DOUBLE values but some queries select BINARY_FLOAT values, BINARY_FLOAT values are returned.
If all queries select NUMBER values, NUMBER values are returned.

In queries that use set operators, OceanBase Database does not execute implicit conversions across data type groups. If corresponding expressions in compound queries are parsed as both character data and numeric data, OceanBase Database returns an error.

Limitations on set operators

Set operators are subject to the following limitations:

Set operators are invalid for the BLOB and CLOB columns.
If the SELECT list prior to a set operator contains an expression, a column alias must be provided for the expression so that it can be referenced in the ORDER BY clause.
You cannot use set operators to specify the UPDATE statement.
You cannot specify the ORDER BY statement in subqueries with these operators.
You cannot use these set operators in a SELECT statement that contains a table collection expression.

UNION and UNION ALL operators

The UNION operator is used to merge the result sets of two or more SELECT statements. The SELECT statements inside UNION must have the same number of columns. The columns must have similar data types. In addition, the sequence of columns must be the same in all SELECT statements. You can set the attribute of UNION to UNION ALL or UNION DISTINCT (or UNIQUE only), respectively indicating that duplicate values are retained or eliminated in the result set. The attribute of other set operators cannot be set to ALL, because they only have the DISTINCT attribute. The default attribute of all set operators is DISTINCT. That is, the UNION operator eliminates duplicate values.

Syntax

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

INTERSECT operator

The INTERSECT operator is used to return the intersection of two result sets, that is, the non-duplicate values returned by both queries.

Syntax

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

Limitations

Both queries must have the same number of columns and the same column sequence.
Columns in the result sets of both queries may have different data types but their data types must be compatible.
Columns in the result sets of both queries must not be of data types that cannot be compared, such as XML, TEXT, NTEXT, IMAGE, and non-binary CLR user-defined types.
The column names in the result sets must be the same as those returned by the query to the left of the operator. The column names or alias in the ORDER BY clause must be the same as that returned by the query on the left side.
The INTERSECT operator must not be used together with the COMPUTE or COMPUTE BY clause.
When two rows are compared to identify non-duplicate values, two NULL values are considered equal.

MINUS operator

The MINUS operator returns the difference between two result sets, namely, all non-duplicate values that appear in the result set of the query on the left side but do not appear in the result set of the query on the right side.

Note

OceanBase Database supports only the MINUS operator and does not support the EXCEPT operator, though they are semantically equivalent.

Syntax

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

Execution sequence

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

Execute the expression included in parentheses.
Execute the INTERSECT operator.
Execute the MINUS and UNION operators from left to right based on their positions in the expression.

If the MINUS or INTERSECT operator is used to compare three or more result sets, data type conversion is performed by comparing the result sets of two queries at a time, and the aforementioned expression evaluation rules are followed.

Examples

Create two tables respectively named table_a and table_b, and insert data into them.

CREATE TABLE table_a(PK INT, name VARCHAR(25));
INSERT INTO table_a VALUES(1,'Fox');
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,'Fox');
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');

Example of UNION

obclient> SELECT PK, name FROM table_a
      UNION
      SELECT PK, name FROM table_b;
+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | Fox    |
|    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

Example of UNION ALL

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

Example of INTERSECT

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

Example of MINUS

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