JOIN, UNION, MINUS, INTERSECT, EXCEPT transformations

Overview

Set operations allow the results of multiple queries to be combined into a single result set. Set operators include JOIN, UNION, INTERSECT, and EXCEPT.

Set operations when working with the same database server

If you need to apply set operations when working with the same relational database server, simply use SQL transformation and the database-specific syntax for set operations.

Set operations when working with multiple heterogeneous data sources

It is easy to work with a single relational database. What if you need to combine the data from the different databases (for example, SQL Server and Postgres) or from database, file, and API endpoint?  

Use the following technique to create a single result set by applying set operations to multiple heterogeneous data sources:

Step 1. Create connections for all your data sources.

Step 2. Create a destination Connection.

Step 3. Create a Connection to the temporary database. 

Step 4. Create Any to Any ETL Flow.

Step 5. Create as many source-to-destination transformations as you have data sources. For each transformation, use a Connection to the temporary database created in step 3 as a destination Connection. Make sure you are loading data into different tables in the temporary database. 

All tables will be created automatically from the source (and will be automatically dropped), so you don't have to worry about creating tables manually.

Step 6. Create a final source-to-destination transformation where the source Connection is a Connection to the temporary database created in step 3, and the destination Connection is a Connection created in step 2. 

Enter the SQL with set operations into the Source query field. Learn SQLite query syntax.