How to Write a Cross-Connection Query Correctly

Many people encounter problems when using the Cross-Connection Editor or Cross-Connection Query Builder to write a query. Sometimes they ask questions such as: What kind of SQL syntax should I use? Can I use the target database’s function in the query? These are all good questions. This article provides information that will help you understand how to use the correct syntax when writing a query in the Cross-Connection Editor or Query Builder.

1. Syntax for identifiers and strings in a cross-connection query

Identifiers (e.g., schema name, connection name, table name, owner name, alias name, etc.)	Use the backtick (backquote) character or nothing: `xxx` or xxx (If xxx has a space in it, you must use the backtick character: `xxx`)	For example: select col from `some_connection`.`some_schema`.`table1`
String	Enclose the string in single quotes: ‘xxx’ (Note: “xxx” is NOT supported)	For example: select `col` from `some_connection`.`some_schema`.`table1` where `col` = ‘abc’

Identifiers (e.g., schema name, connection name, table name, owner name, alias name, etc.)

Use the backtick (backquote) character or nothing: `xxx` or xxx

(If xxx has a space in it, you must use the backtick character: `xxx`)

For example: select col from `some_connection`.`some_schema`.`table1`

String

Enclose the string in single quotes: ‘xxx’

(Note: “xxx” is NOT supported)

For example: select `col` from `some_connection`.`some_schema`.`table1` where `col` = ‘abc’

Note: Do not use double quotes (“xxx”) in a cross-connection query.

2. What syntax should I use?

In most cases, you can use MySQL syntax. But in some cases, it is recommended or even required to use the target database’s native syntax. It depends on how you write the query, what kind of target data source you are using, as well as some other factors.

To make things simple, this article provides several rules for you to follow. Now let’s start from the simplest query: a single-connection query.

2.1 Single-connection query

You may write a query in which the tables are all from a single data source

Typical single connection query example

select col_1,col_2

from oracle_conn.test_db1.table_a A join oracle_conn.test_db2.table_b B

on A.id = B.id

( note: all databases and tables are from the same connection: oracle_conn )

Rule 1: (Single-Connection) As long as the target data source supports SQL, an attempt is made to pushdown the whole query to the remote data source and execute the query there. So the target database’s SQL syntax is always recommended.

That is to say, if the target data source is Oracle, feel free to use Oracle functions in your query,

See an example

Applied datasources

File Data Source	CSV File, EXCEL, ACCESS
Relational Data Source	Amazon Redshift, Greenplum, MySQL, Oracle, SQL Server, SQL Azure, ODBC Generic, DB2, IBM Netezza, MariaDB, SQL Azure, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, SAP SQL Anywhere, Teradata, Vertica
Business Intelligence	Salesforce
NoSQL	Apache Hive

Rule 2: (Single-Connection) If the target data source is SQL Server, Oracle or Salesforce, you may even be allowed to use limited MySQL syntax in your query.

This limited MySQL syntax includes some commonly-used MySQL functions, “limit xxx,” and “order by xxx,” all of which are listed in Limited MySQL Syntax at the end of this article. Feel free to use them(no matter if they are marked by or not) for the specified data sources. If you use a function that is NOT on this list, the function is not translated. It is treated as the target database’s syntax and is pushed down.

Applied datasources

Relational Data Source	Oracle, SQL Server, SQL Azure, ODBC Generic
Business Intelligence	Salesforce

Note: The data sources above which are connected by Generic ODBC are not included.

Example

Background Knowledge

You may be curious about how we can achieve this.

Answer:

1) The MySQL syntax is translated (from MySQL syntax to target database’s syntax), which enables the whole query to be pushed down to the remote data source. — This is not only for the convenience of users who are familiar with MySQL syntax, but it also improves performance. See example 2.

2) For the syntax on this list that has not been translated, please note the following:

– Some functions or syntax is already supported by the target data source, and we can detect these. So in these cases, push down of the query would also be enabled.

– Because translation requires a large effort, only the most commonly-used syntax and functions are covered. For other functions in the list that have not yet been translated, all table data is fetched from the remote data source and then filtered locally. — This method does not provide optimal performance, but for users familiar with MySQL only, it is quite convenient.

Rule 3: (Single-Connection) For data sources that don’t support SQL, use MySQL syntax.

Applied datasources

Business Intelligence	Generic OData, Google Analytics, SSAS, Oracle BI, SharePoint, SAP
NoSQL	DynamoDB, MongoDB, Cassandra, HBASE, Impala, Azure Table Service

Example

Tip: In order to improve query performance, please specify the column names that you really need to use and add some filters.

2.2. Cross-connection query

Users more often open the Cross-Connection Query Builder or Editor to do a query that uses multiple connections rather than a single connection.

Typical multiple connection query

select col_1,col_2

from mysql_conn.test_db1.table_a A join mongo_conn.test_db1.table_b B

on A.id = B.id

(Note: table_a is from a mysql table, table_b is from a mongo collection. They are from different connections.)

It’s a little more complex, but some good examples can help you understand the rules.

Rule 4: (Multiple-Connection) The outer query of the “join query” should use MySQL syntax.

See an outer query

Examples

example 1:

example 2:

Background knowledge

The embedded hub that does the cross-connection query is actually a MySQL plugin engine. It fetches data from the different data sources separately, and then finally does the join operation in the embedded hub (MySQL). So the outer query (final query) of the join query must use MySQL syntax.

Tip: If you really need to use the target database’s syntax, reconstruct the query such that you use the target database’s syntax in a subquery. See Question 1 of 3.Troubleshooting.

Rule 5: (Multiple-Connection) In the subquery which can be pushed down to the remote database, try to use the target database’s syntax.

See a sub-query

Examples

Background knowledge

In order to improve performance, the subquery will always be pushed down to the remote data source if possible.

Tips: 1) If the subquery is a single-connection query, use syntax according to the rules in Single connection query. If not, use MySQL syntax.

2) How do you check whether or not a subquery can be pushed down?

Answer: All tables in the query must be from a single connection and the target data source must support SQL.

Rule 6: (Multiple-Connection) Check queries in an embedded query to see if each can be pushed down on its current level, without worrying about its subqueries or outer queries. If the query can be pushed down, try to use the target databases’s syntax.

See an embedded query

Examples

example 1:

example 2:

Background knowledge:

In order to improve performance, queries in an embedded query will always be pushed down from inner to outer, if possible.

Tip: If the current-level query is a single-connection query, use syntax according to the rules in Single connection query. If not, use MySQL syntax.

3. Trouble shooting

Question 1: In the outer query of a join query, I need to apply an oracle function on one selected column which is got from an oracle table , what should I do?

Answer: Reconstruct your join query such that the target databases’s function on the selected column is included in a subquery.

For example, if you intend to write a query like this:

Then we would suggest you reconstruct your query like this:

Question 2: How do I know if a query or a subquery will really be pushed down?

Answer: If the query or sub-query is a single-connection query, and the target data source supports SQL, an attempt is always made to push the query down to the remote data source. However, sometimes you might use MySQL syntax which has not been translated to the target databases’s syntax, in this case, pushdown would not happen, all of the table data will be fetched from the remote table and the query will be executed locally.

A simple way to learn if a query is actually pushed down or not is to execute the query and then check the “Explain Plan.”

For example, if the whole query is pushed down, you will see “Push-Down Query” in the Explain Plan tab.

If a query is not pushed down, the query is finally executed locally, and you will see “Data Hub Query” in the Explain Plan tab.

To check if a subquery is pushed down, you can also check the “Explain Plan” tab. The following example shows that the subquery was pushed down first, and then the join operation was done locally.

Question 3: How do I specify a query object in Cross-connection query editor ?

Answer: A query object name is made up of several components: connection name, user/schema name, and table name. The format of the name and the number of components varies depending on the data source. The example below is an oracle connection object.

The best way to get the exact object name is to open a Cross-Connection SQL Editor window and drag the table you want to query to the Editor. In the menu that opens, select “Insert Name.” This action inserts the exact name.

4. Useful Tips

1) In order to improve query performance, please specify the column names that you really need to use and add some filters. Otherwise, an attempt is made to fetch all data from the remote table.

2) If you find it difficult to use the Cross-Connection SQL Editor to write a query, you can use the Cross-Connection Query Builder. The Query Builder allows you to build a query without writing a SQL statement.

Additional Information

Limited MySQL syntax list used by target data source: SQL Server, Oracle, Salesforce

MySQL functions:

string functions				time functions				date functions				miscellaneous functions				aggregate functions
function name	translation has been done for:			function name	translation has been done for:			function name	translation has been done for:			function name	translation has been done for:			function name	translation has been done for:
function name	Oracle	SQL Server	Salesforce	function name	Oracle	SQL Server	Salesforce	function name	Oracle	SQL Server	Salesforce	function name	Oracle	SQL Server	Salesforce	function name	Oracle	SQL Server	Salesforce
SUBSTRING				NOW				YEAR				ISNULL				COUNT
SUBSTR				CURRENT_TIMESTAMP				QUARTER				IFNULL
LENGTH				CURRENT_DATE				MONTH				TRIM
LOCATE				CURRENT_TIME				DAY				CONVERT_TZ
CONCAT				CURDATE				DAYOFMONTH
LEFT				CURTIME				HOUR
RIGHT				LOCALTIME				MINUTE
				LOCALTIMESTAMP				SECOND
								DAYOFYEAR
								YEARWEEK
								WEEKOFYEAR
								WEEK
								WEEKDAY
								DAYOFWEEK
								MICROSECOND
								MONTHNAME
								DAYNAME
								TO_DAYS
								TO_SECONDS
								DATE_FORMAT
								ADDDATE
								ADDTIME
								DATE_ADD
								DATE_SUB
								DATEDIFF
								SUBDATE
								SUBTIME
								TIMESTAMPADD
								TIMESTAMPDIFF
								DATE
								TIME
								MAKEDATE
								MAKETIME
								TIMESTAMP

limit xxx: translation has been done for SQL Server, Oracle

order by xxx: translation has been done for SQL Server

1. Syntax for identifiers and strings in a cross-connection query

2. What syntax should I use?

2.1 Single-connection query

2.2. Cross-connection query

Examples

Examples

3. Trouble shooting

4. Useful Tips

Additional Information

Limited MySQL syntax list used by target data source: SQL Server, Oracle, Salesforce

About the Author

Winnie Wu

About the Author

Start the discussion at forums.toadworld.com