How to make cross database queries in MySQL

Sometimes you may need to fetch data from multiple databases in a single SQL query in MySQL. In such cases, you will need to create a cross database query in MySQL. In this article, we will learn how to setup cross database queries in MySQL.

How to make cross database queries in MySQL

Here is how to create cross database queries in MySQL. Let us say you have two database tables t1 and t2 in databases db1 and db2 respectively on same server. Here is the MySQL query to select rows from both tables.

mysql> select * from db1.t1, db2.t2;

In the above query, you need to address each table by prefixing it with its database name. For example, you need to use db1.t1 and tb2.t2 instead of just t1 and t2.

Here is another query to create a join between two tables, on column id.

mysql> SELECT *
  FROM db1.t1 table1
  JOIN db2.t2 table2 ON table2.id = table1.id;

In the above query, we are using aliases table1 and table2 for db1.t1 and db2.t2 respectively, to make it easier to recall them in the query.

Please note, you need to have a common database and username and password for both the databases, for the above query to work. Also, you can modify the above queries to fetch data from not just 2 but more databases, as long as the same database user has access to all the databases referenced in your MySQL query.

In this short article, we have learnt how to easily setup cross database queries in MySQL.

Also read:

How to Copy/Transfer data to Another database in PostgreSQL
How to Count Repeated Characters in Python String
How to Write Limit Query in MongoDB
How to Copy/Transfer data in MySQL
How to Setup Uwsgi with NGINX in Python