How to Use Joins in ParadeDB in PostgreSQL

How do you use joins in ParadeDB to combine tables effectively?

Joins in ParadeDB combine rows from two or more tables based on related columns, enabling richer, multi-table analytics.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What problem do joins solve in ParadeDB?

Joins let you read related data that lives in separate tables—customers with their orders, orders with their items, or products with current stock—without duplicating rows or denormalizing schemas.

Which join types does ParadeDB support?

ParadeDB inherits PostgreSQL’s INNER, LEFT, RIGHT, and FULL joins, plus CROSS join and LATERAL joins. All types work the same way you already know from core Postgres.

How do I write a basic INNER JOIN?

Start with SELECT columns FROM table1 INNER JOIN table2 ON table1.col = table2.col;. Replace the table and column names with your ParadeDB tables.

How can I join three or more tables?

Chain the joins: ... FROM A JOIN B ON ... JOIN C ON .... Each additional join adds another ON clause that links the new table to an already-joined result.

When should I use LEFT JOIN instead of INNER?

Use LEFT JOIN when you must keep all rows from the left table even if the right table lacks matches—e.g., show customers who have not yet placed orders.

Can ParadeDB joins leverage indexes?

Yes. ParadeDB uses PostgreSQL’s planner. Creating indexes on join keys (customer_id, product_id, etc.) speeds lookups and reduces disk I/O.

Best practices for performant joins?

Filter early with WHERE clauses, project only needed columns, and ensure join keys are indexed. For very large datasets, consider partitioning or materialized views.

Example: customer lifetime value (CLV)

The query in the next section calculates each customer’s total spend by joining Customers, Orders, and OrderItems.

Common mistakes to avoid?

Omitting a join condition causes a Cartesian product; forgetting to index join keys slows queries dramatically. See details below.

Need a ready-to-run template?

Copy the example query, swap table names if yours differ, and adapt the WHERE filters.

FAQ

Is ParadeDB syntax different from PostgreSQL joins?

No. ParadeDB is Postgres compatible; the join syntax is identical.

Can I join a ParadeDB hypertable with a regular table?

Yes. ParadeDB supports mixing storage types in joins, but make sure time partitions are pruned with appropriate WHERE clauses.

Why How to Use Joins in ParadeDB in PostgreSQL is important

How to Use Joins in ParadeDB in PostgreSQL Example Usage


-- Calculate total revenue per customer
SELECT c.id,
       c.name,
       SUM(o.total_amount) AS lifetime_value
FROM   Customers  AS c
LEFT JOIN Orders AS o ON o.customer_id = c.id
GROUP  BY c.id, c.name
ORDER  BY lifetime_value DESC;

How to Use Joins in ParadeDB in PostgreSQL Syntax


-- Generic join syntax
SELECT select_list
FROM   table1 [AS t1]
        JOIN_TYPE JOIN table2 [AS t2]
        ON join_condition
[WHERE  filter_expression]
[GROUP  BY columns]
[ORDER  BY columns];

-- JOIN_TYPE can be:
INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] | CROSS | LATERAL

-- Ecommerce example: orders with customer and product info
SELECT c.id, c.name, o.id AS order_id, p.name AS product_name, oi.quantity, o.total_amount
FROM   Customers     AS c
JOIN   Orders        AS o  ON o.customer_id = c.id
JOIN   OrderItems    AS oi ON oi.order_id    = o.id
JOIN   Products      AS p  ON p.id           = oi.product_id;

Common Mistakes

Mistake: Leaving out the ON clause when adding a new JOIN, creating a massive Cartesian product. Fix: Always specify a precise condition that links the new table to an existing one.
Mistake: Joining large tables on non-indexed columns, leading to sequential scans. Fix: Create B-tree indexes on frequently joined keys such as customer_id or product_id.

Frequently Asked Questions (FAQs)

Do joins work with ParadeDB vector columns?

Yes. Vector columns can participate in joins, but equality comparisons must use the exact same stored vector. Nearest-neighbor search is done with <-> operators, not joins.