All Products
Search
Document Center

Set operations

Last Updated: Jan 10, 2020

The set operations of the database can combine the results of multiple queries into a result data set. Set operations include:

  • UNION
  • INTERSECT
  • EXCEPT/MINUS

    ApsaraDB for OceanBase supports both EXCEPT and MINUS. The usage of the two are the same. Note that the number of columns of the data sets in the set operation must be the same, and the data types of the columns must also match. For UNION, the available UNION attributes are ALL and DISTINCT/UNIQUE. ALL indicates that duplicated rows are allowed in the set. DISTINCT/UNIQUE indicates that duplicated rows are not allowed in the set. For other set operations, you can only use the DISTINCT keyword and cannot use the ALL keyword. By default, DISTINCT is used for all set operations. You can use the ORDER BY and LIMIT clauses in set operations. Other clauses are not allowed, shown as follows.

  1. OceanBase (root@test)> create table t1(a int primary key, b int, c int);
  2. Query OK, 0 rows affected (0.16 sec)
  3. OceanBase (root@test)> create table t2(a int primary key, b int, c int);
  4. Query OK, 0 rows affected (0.10 sec)
  5. --supports the ORDER BY and LIMIT clauses in UNION statements
  6. OceanBase (root@test)> (select * from t1 union all select * from t2) order by a limit 10;
  7. Empty set (0.02 sec)
  8. --other clauses are not supported, for example, GROUP BY.
  9. OceanBase (root@test)> OceanBase (root@test)> (select * from t1 union all select * from t2) group by a limit 10;
  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 'OceanBase (root@test)> (select * from t1 union all select * from t2) group by a ' at line 1

UNION

In the following example, UNION returns all the unique rows in t1 and t2.

  1. OceanBase (root@test)> create table t1(a int, b int, c int);
  2. Query OK, 0 rows affected (0.12 sec)
  3. OceanBase (root@test)> create table t2(a int, b int, c int);
  4. Query OK, 0 rows affected (0.11 sec)
  5. OceanBase (root@test)> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
  6. Query OK, 3 rows affected (0.07 sec)
  7. Records: 3 Duplicates: 0 Warnings: 0
  8. OceanBase (root@test)> insert into t2 values (2,2,2),(3,3,3),(4,4,4);
  9. Query OK, 3 rows affected (0.02 sec)
  10. Records: 3 Duplicates: 0 Warnings: 0
  11. OceanBase (root@test)> select * from t1 union select * from t2;
  12. +------+------+------+
  13. | a | b | c |
  14. +------+------+------+
  15. | 1 | 1 | 1 |
  16. | 2 | 2 | 2 |
  17. | 3 | 3 | 3 |
  18. | 4 | 4 | 4 |
  19. +------+------+------+
  20. 4 rows in set (0.01 sec)

UNION All

In the following example, UNION All returns all rows in t1 and t2 including duplicate rows.

  1. OceanBase (root@test)> create table t1(a int, b int, c int);
  2. Query OK, 0 rows affected (0.12 sec)
  3. OceanBase (root@test)> create table t2(a int, b int, c int);
  4. Query OK, 0 rows affected (0.11 sec)
  5. OceanBase (root@test)> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
  6. Query OK, 3 rows affected (0.07 sec)
  7. Records: 3 Duplicates: 0 Warnings: 0
  8. OceanBase (root@test)> insert into t1 values (2,2,2),(3,3,3),(4,4,4);
  9. Query OK, 3 rows affected (0.02 sec)
  10. Records: 3 Duplicates: 0 Warnings: 0
  11. OceanBase (root@test)> select * from t1 union all select * from t2;
  12. +------+------+------+
  13. | a | b | c |
  14. +------+------+------+
  15. | 1 | 1 | 1 |
  16. | 2 | 2 | 2 |
  17. | 3 | 3 | 3 |
  18. | 2 | 2 | 2 |
  19. | 3 | 3 | 3 |
  20. | 4 | 4 | 4 |
  21. +------+------+------+
  22. 6 rows in set (0.02 sec)

INTERSECT

In the following example, INTERSECT returns the rows that appear in both t1 and t2 without duplicate rows.

  1. OceanBase (root@test)> create table t1(a int, b int, c int);
  2. Query OK, 0 rows affected (0.12 sec)
  3. OceanBase (root@test)> create table t2(a int, b int, c int);
  4. Query OK, 0 rows affected (0.12 sec)
  5. OceanBase (root@test)> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
  6. Query OK, 3 rows affected (0.02 sec)
  7. Records: 3 Duplicates: 0 Warnings: 0
  8. OceanBase (root@test)> insert into t2 values (2,2,2),(3,3,3),(3,3,3),(4,4,4);
  9. Query OK, 4 rows affected (0.01 sec)
  10. Records: 4 Duplicates: 0 Warnings: 0
  11. OceanBase (root@test)> select * from t1 intersect select * from t2;
  12. +------+------+------+
  13. | a | b | c |
  14. +------+------+------+
  15. | 2 | 2 | 2 |
  16. | 3 | 3 | 3 |
  17. +------+------+------+
  18. 2 rows in set (0.01 sec)

EXCEPT/MINUS

In the following example, EXCEPT/MINUS returns the rows that are in t1 and not in t2 without duplicate rows.

  1. OceanBase (root@test)> create table t1(a int, b int, c int);
  2. Query OK, 0 rows affected (0.12 sec)
  3. OceanBase (root@test)> create table t2(a int, b int, c int);
  4. Query OK, 0 rows affected (0.12 sec)
  5. OceanBase (root@test)> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
  6. Query OK, 3 rows affected (0.02 sec)
  7. Records: 3 Duplicates: 0 Warnings: 0
  8. OceanBase (root@test)> insert into t2 values (2,2,2),(3,3,3),(3,3,3),(4,4,4);
  9. Query OK, 4 rows affected (0.01 sec)
  10. Records: 4 Duplicates: 0 Warnings: 0
  11. OceanBase (root@test)> select * from t1 except select * from t2;
  12. +------+------+------+
  13. | a | b | c |
  14. +------+------+------+
  15. | 1 | 1 | 1 |
  16. +------+------+------+
  17. 1 row in set (0.02 sec)