Index

Creating Cross-Cluster Queries

Note Prerequisite for performing cross-cluster queries is that you have configured your database with multiple schemas (not necessary for MySQL), and granted one user SELECT privileges an all the objects in all of the schemas. You must use this user when connecting to the database to perform cross-cluster queries.

Although you could JOIN table from one schema with a one from other schema, it might not be useful to do that, as the data comes from separate SGE cluster. However, queries that combine together the results of two or more separate queries could be beneficial.

The syntax is:

select_statement1 UNION [ALL] select_statement2
select_statement1 INTERSECT [ALL] select_statement2
select_statement1 EXCEPT [ALL] select_statement2

The select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clause. These clauses can be appended at the end of all the chained UNION, INTERSECT and EXCEPT queries, which will then be applied to the combined returned result.

UNION effectively appends the result of select_statement1 to the result of select_statement2 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used.

INTERSECT returns all rows that are both in the result of select_statement1 and in the result of select_statement2. Duplicate rows are eliminated, unless INTERSECT ALL is used

EXCEPT returns all rows that are in the result of select_statement1 but not in the result of select_statement2. Duplicate rows are eliminated, unless EXCEPT ALL is used.

In order to calculate the union, intersection, or difference of two queries, the two queries must be "union compatible", which means that they return the same number of columns and the corresponding columns have compatible data types. The query statements must use the fully qualified object names, i.e. schemaname.tablename, schemaname.viewname, respectively.

Note In MySQL the syntax would be database.tablename, database.viewname, respectively.

Examples

The following two tables are in schema arco_write_london and arco_write_denver:

Example – arco_write_london.sge_user

+------+--------+
| u_id | u_user |
+------+--------+
|    1 | jade   |
|    2 | julie  |
|    3 | john   |
+------+--------+

Example – arco_write_denver.sge_user

+------+--------+
| u_id | u_user |
+------+--------+
|    1 | john   |
|    2 | david  |
|    3 | rose   |
+------+--------+

Connected as the multi_read user who has privileges on all the schemas we execute the following queries:

1. SELECT u_user FROM arco_write_london.sge_user UNION SELECT u_user FROM arco_write_denver.sge_user;

   +--------+
   | u_user |
   +--------+
   | david  |
   | jade   |
   | john   |
   | julie  |
   | rose   |
   +--------+
   

   
   

2. SELECT u_user from arco_write_london.sge_user UNION ALL SELECT u_user FROM arco_write_denver.sge_user;

   +--------+
   | u_user |
   +--------+
   | jade   |
   | julie  |
   | john   |
   | john   |
   | david  |
   | rose   |
   +--------+
   

   
   

3. SELECT u_user FROM arco_write_london.sge_user INTERSECT SELECT u_user FROM arco_write_denver.sge_user;

   +--------+
   | u_user |
   +--------+
   | john   |
   +--------+
   

   
   

4. SELECT u_user FROM arco_write_london.sge_user EXCEPT SELECT u_user FROM arco_write_denver.sge_user;

   +--------+
   | u_user |
   +--------+
   | jade   |
   | julie  |
   +--------+