All Products
Search
Document Center

Set

Last Updated: Sep 29, 2021

You can use set operations in the database to combine the results of multiple queries into a result set. Set operations include:

  • UNION

  • INTERSECT

  • EXCEPT/MINUS

Note that the query results involved in a set operation must be consistent in the number of columns and the data types must be compatible. For the UNION operation, you can set the attribute of UNION to ALL, DISTINCT, or UNIQUE. ALL indicates that rows in the result set can be duplicate. DISTINCT and UNIQUE indicate that rows in the result set must be unique. The attribute of other set operations cannot be set to ALL, because they only have the DISTINCT attribute. The default attributes of all set operations are DISTINCT. OceanBase Database allows you to specify ORDER BY and LIMIT clauses in set operations, but you cannot specify other clauses. Example:

obclient> create table t1(a int primary key, B int, c int);
Query OK, 0 rows affected (0.16 sec)
obclient> create table t2(a int primary key, b int, c int);
Query OK, 0 rows affected (0.10 sec)

-- The ORDER BY and LIMIT clauses are allowed in a UNION statement.
obclient> (select * from t1 union all select * from t2) order by a limit 10;
Empty set (0.02 sec)

-- Clauses other than ORDER BY and LIMIT, for example, GROUP BY, are not allowed in a UNION statement.
obclient> (select * from t1 union all select * from t2) group by a limit 10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'group by a limit 10' at line 1

Example of UNION

This example retrieves all unique rows in Table t1 and Table t2.

obclient> create table t1(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> create table t2(a int, b int, c int);
Query OK, 0 rows affected (0.11 sec)
obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> insert into t2 values (2,2,2),(3,3,3),(4,4,4);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> select * from t1 union select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
|    4 |    4 |    4 |
+------+------+------+
4 rows in set (0.01 sec)

Example of UNION All

This example retrieves all rows in t1 and t2 without deduplication.

obclient> create table t1(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> create table t2(a int, b int, c int);
Query OK, 0 rows affected (0.11 sec)
obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> insert into t1 values (2,2,2),(3,3,3),(4,4,4);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> select * from t1 union all select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
|    4 |    4 |    4 |
+------+------+------+
2 rows in set (0.02 sec)

Example of INTERSECT

This example retrieves the rows that exist in both t1 and t2 and removes the duplicate rows.

obclient> create table t1(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> create table t2(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> insert into t2 values (2,2,2),(3,3,3),(3,3,3),(4,4,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> select * from t1 intersect select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+
2 rows in set (0.01 sec)

Example of EXCEPT/MINUS

This example retrieves the rows that exist in t1 but not in t2 and removes the duplicate rows.

obclient> create table t1(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> create table t2(a int, b int, c int);
Query OK, 0 rows affected (0.12 sec)
obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> insert into t2 values (2,2,2),(3,3,3),(3,3,3),(4,4,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> select * from t1 except select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
1 row in set (0.02 sec)