Joining tables from distinct databases

info

SQL support in RAW is currently in Closed Beta. Contact us for early access.

info

This document is a work-in-progress. Check back soon!

In this example, we'll demonstrate how to join data from two different remote databases: PostgreSQL and MySQL. We'll use the trips table from a MySQL database and the airports table from a PostgreSQL database (refer to this example)

First, establish a connection to your MySQL database by adding it as a RAW credential. Follow the same steps as mentioned earlier for PostgreSQL, adjusting for the MySQL database specifics.

Assuming there is a trips table in MySQL with a destination column, and the airports table in PostgreSQL with an iata_faa column, you can join these tables based on these columns. Here's an example SQL query:

SELECT
    trips.*, airports.name, airports.city
FROM
    mysql_credential.trips
JOIN
    postgres_credential.airports
ON
    trips.destination = airports.iata_faa;

In this query, mysql_credential and postgres_credential are the names of the credentials you've set up for your MySQL and PostgreSQL databases, respectively.

This query retrieves all columns from the trips table and includes the name and city of the airport from the airports table. It joins these tables based on the destination column in trips matching the iata_faa column in airports.