Joins

Joins merges the current dataset with a table from any connected integration using a shared key column. Four join types: Left, Right, Inner, and Outer. Select the Left Key from the current dataset, choose a join type, then select the database, table, and Right Key from the second table. Conflicting column names in the joined table get a _right suffix automatically.

The second table can come from a different database or integration than the current pipeline source. No need to land both datasets in a shared warehouse first.

Cross-Database Joins

Most ETL and transformation tools require both tables to exist in the same warehouse before a join is possible. Edilitics does not.

Join any two tables across any two connected integrations in a single operation - PostgreSQL to BigQuery, MySQL to Snowflake, MongoDB to DuckDB. No staging required, no intermediate landing zone, no SQL to write.

This is a meaningful capability gap versus warehouse-first tools like dbt, Fivetran, Airbyte, and Matillion - all of which require data to be landed in a common warehouse before any join can be performed.

When to Use Joins

• Enrich orders with customer data. Left join orders to customers on customer_id to add name, email, and region to every order row.
• Find matching records only. Inner join transactions to accounts to return only transactions with a valid matching account.
• Retain all records from a reference table. Right join orders to a product_catalog to keep every product, even those with no orders.
• Build a full combined view. Outer join two regional sales tables to see all rows from both, with nulls where no match exists.
• Join across databases. Left join a PostgreSQL orders table to a BigQuery metadata table without moving either dataset.

Sample Dataset

The examples in this doc use two sample datasets. Download both to follow along in your own workspace.

Join Types

How to Apply a Join

Column Conflict Handling

When the second table contains a column with the same name as a column in the current dataset (other than the join key), the column from the second table is renamed automatically with a _right suffix.

The join key column from the second table (Right Key) is dropped from the output. Only the Left Key column is retained.

Before and After

Left Join

Left join edilitics_sample_orders (500 rows, 13 columns) to edilitics_sample_products (4 rows, 7 columns) on product_id. All 500 order rows retained. Matching product columns added. Orders with a product_id not in the products table return null for product columns.

Left Key: product_id (orders). Right Key: product_id (products).

Input (current dataset, first 3 rows):

Second table (edilitics_sample_products):

Output (first 3 rows):

Output: 500 rows, 19 columns (13 original + 6 from products; product_id from right table dropped, product_tier from products renamed product_tier_right to avoid conflict).

Inner Join

Same tables, Inner Join on product_id. Only order rows with a matching product_id in edilitics_sample_products are returned. All 500 orders reference PROD-001 through PROD-004 - all match - so output remains 500 rows. Orders referencing a discontinued or unknown product_id would be excluded.

Code Equivalent

-- Left Join
SELECT o.*, p.product_name, p.category, p.list_price
FROM orders o
LEFT JOIN product_catalog p ON o.product_id = p.product_id;

-- Inner Join
SELECT o.*, p.product_name, p.category, p.list_price
FROM orders o
INNER JOIN product_catalog p ON o.product_id = p.product_id;

-- Right Join
SELECT o.order_id, o.revenue, p.*
FROM orders o
RIGHT JOIN product_catalog p ON o.product_id = p.product_id;

-- Outer Join
SELECT o.*, p.product_name, p.category, p.list_price
FROM orders o
FULL OUTER JOIN product_catalog p ON o.product_id = p.product_id;

import polars as pl

# Left Join
df_result = df_orders.join(
    df_catalog,
    left_on="product_id",
    right_on="product_id",
    how="left"
)

# Inner Join
df_result = df_orders.join(
    df_catalog,
    left_on="product_id",
    right_on="product_id",
    how="inner"
)

# Right Join (flip left join for full right-table retention)
df_result = df_catalog.join(
    df_orders,
    left_on="product_id",
    right_on="product_id",
    how="left"
)

# Outer Join
df_result = df_orders.join(
    df_catalog,
    left_on="product_id",
    right_on="product_id",
    how="outer"
)

Operation Reference

Frequently Asked Questions

Next Steps

Need help? Email support@edilitics.com with your workspace, job ID, and context. We reply within one business day.