With Master-Slave topologies and modern practices such as Database sharding becoming increasingly ubiquitous, database administrators (DBAs) and developers are working with multiple databases more than ever before. Doing so is made a lot easier by software that can accommodate multiple database connections.

That's where Navicat Premium comes in. It's a database development, administration and management tool that allows you to simultaneously connect to MySQL, MariaDB, MongoDB, SQL Server, Oracle, PostgreSQL, and SQLite databases. Navicat is also compatible with most cloud databases, including Amazon RDS, Amazon Aurora, Amazon Redshift, Microsoft Azure, Oracle Cloud, Google Cloud, Alibaba Cloud, Tencent Cloud, MongoDB Atlas and Huawei Cloud.

In today's blog, we'll learn how to construct and execute a SELECT query that will fetch data from multiple databases using navicat Premium's SQL Editor.

Setting up the Environment

We'll be needing a couple of tables, each within their own database. As it happens, I've got a few copies of the Sakila Sample Database. I've created copies of the actors table and split its contents down the middle, so that names starting with A to L are in the first database and names starting with M to Z are in the other. That will allow us to combine the two groups of names into one result set. Here is their layout in the Navicat object pane:

Multiple Database SELECT Syntax

Just as you can refer to a table within the default database as tbl_name in your SELECT statements, you can also prefix the table name with the database name, e.g. db_name.tbl_name, to specify a database explicitly. The database prefix can also be employed to combine different databases within one SELECT statement's table list, as specified after the FROM keyword. Hence, the following is valid SQL:

SELECT database1.table1.field1,
       database2.table1.field1
FROM database1.table1,
      database.table1
WHERE database1.table1.age > 12;

Using Table JOINs

You can JOIN tables just as you normally would; just be sure to fully qualify the table names by prepending the database name:

SELECT *
FROM database1.table1 T1
JOIN database2.table1 AS T2 ON T1.id = T2.id

If you don't need to JOIN the tables on a common field, you can combine multiple SELECTs using the UNION operator:

SELECT *
 FROM database1.table1 T1
 WHERE T1.age > 12
UNION
SELECT *
 FROM database2.table1 T2
 WHERE T2.age > 12;

Now that we know how to query two tables at a time, let's try out a similar query on our actors table. We'll SELECT actors whose IDs are between a certain range:

SELECT T1.actor_id,
       T1.first_name,
                         T1.last_name
 FROM sakila.`actor_a-l` T1
 WHERE T1.actor_id BETWEEN 30 AND 50
UNION
SELECT T2.actor_id,
                         T2.first_name,
           T2.last_name
 FROM sakila2.`actor_m-z` T2
 WHERE T2.actor_id BETWEEN 30 AND 50
 ORDER BY last_name;

You can be the results that there are actors who are stored in the A - L table, while some originate from the M - Z table:

Conclusion

In today's blog, we learned how to construct and execute a SELECT query to fetch data from multiple databases using Navicat Premium's SQL Editor. Navicat helps you code fast with Code Completion and customizable Code Snippets by getting suggestions for keywords and stripping the repetition from coding. You can try it for 14 days completely free of charge for evaluation purposes.