DbVisualizer does not in itself support cross database queries. Continue reading for the options.


1) There are dedicated query engines like Presto and Trino that are specifically designed to support cross database queries. Check the respective product documentation how to setup these. Once Presto or Trino is up and running, DbVisualizer 13 supports these to run the queries.


2) Some databases, offer concepts like "database links" to allow cross database queries which can then be run in DbVisualizer. See the documentation for your database for details.


3) The last alternative is to use an H2 database as an intermediary query engine. Continue reading for more information. 


H2 supports something called Linked Table. A Linked Table is a table in another database of any kind that you can connect to through JDBC. So to combine columns from a table in one database with columns from a table in another database, you create Linked Tables for each table in the H2 database and then run a SELECT statement joining the Linked Tables.


Here's an example involving two databases, here called DB_ONE and DB_TWO, each with a table like this:

CREATE TABLE test_link (
   id INT NOT NULL,
   val VARCHAR(20),
   PRIMARY KEY (id)
)

Follow these steps to set up an H2 database with the Linked Tables:

  1. Open Tools->Driver Manager and select the H2 Embedded driver.
  2. Add the JAR files for the database(s) you want to work with to the Driver jar Files list. For instance if both DB_ONE and DB_TWO are MySQL databases, add the MySQL driver JAR file.
  3. Create an H2 Embedded connection using the Connection Wizard, i.e. select H2 Embedded as the driver and enter a file name for the database (e.g. ~/mydb to create it in your home folder). Leave Userid and Password blank.
  4. Connect to the H2 database and navigate to the Linked Tables node. Select it and choose Create Linked Table from the right-click menu.
  5. Enter all information for the table in the DB_ONE database, e.g. DB_ONE_TABLE as the Local Table Name and TEST_LINK as the Remote Table Name. The other fields depend on the databases you want to work with. For MySQL, the JDBC Driver Class Name is com.mysql.jdbc.Driver, and the JDBC URL jdbc:mysql://server:port/database, where server, port and database are replaced with the appropriate values for your database. To find out the JDBC Driver Class and the URL format, open Tools->Driver Manager in DbVisualizer and select the driver you are going to use. The details pane to the right shows the class and URL format.
  6. Next, use the Create Linked Table dialog again to create a Linked Table for the table in the DB_TWO database, e.g. naming it DB_TWO_TABLE.

Now open the SQL Commander and select the H2 database as the Database Connection to execute SQL statements that work with the two tables, for instance: 

SELECT id, val from DB_ONE_TABLE where id in (SELECT id from DB_TWO_TABLE) 

To select the id and val from the DB_ONE_TABLE based on the sub select on the DB_TWO_TABLE.

You can also update one table with data from the other using an UPDATE statement like this:

UPDATE DB_TWO_TABLE r SET val = (
   SELECT
      val
   FROM
   DB_ONE_TABLE
   WHERE
      id = r.id
   )

We suggest you experiment with this using test tables before you do it with your real tables to get a feel for how it works, and watch out for potential data format issues if you combine tables from different database types, such as an Oracle table with a MySQL table.

H2 is an open source product that you can learn more about at http://www.h2database.com. H2 is bundled with DbVisualizer since version 8.0. For any further assistance please consult the H2 community.