---
title: Joins Overview
description: Optimize JOIN queries in ParadeDB
canonical: https://www.paradedb.com/docs/documentation/joins/overview
---

ParadeDB supports all standard PostgreSQL `JOIN` types, including:

- `INNER JOIN`
- `LEFT JOIN`
- `RIGHT JOIN`
- `FULL JOIN`
- `SEMI JOIN`
- `ANTI JOIN`

ParadeDB optimizes standard `JOIN` queries involving search operations through a feature called **join pushdown**.

Our goal is to support and accelerate all `JOIN` queries shaped like Top-K or aggregate queries. Because join pushdown is enabled by default, ParadeDB automatically executes parts of a `JOIN` directly inside the ParadeDB executor for queries that meet the requirements.

If a query does not meet the requirements, ParadeDB falls back to PostgreSQL’s native row-based execution.

## Join Pushdown

Join pushdown reduces query latency by answering as much of the query as possible using the index before touching the underlying tables.

<Note>
  To disable join pushdown for debugging, run `SET
  paradedb.enable_join_custom_scan TO off;`.
</Note>

## Requirements for Join Pushdown

Join pushdown is automatically used when a query meets several conditions. If any of these are not satisfied, PostgreSQL will simply execute the join normally.

| Requirement      | Description                                                                                                                                                      |
| ---------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ParadeDB indexes | All tables participating in the join must have a ParadeDB index.                                                                                                 |
| Search predicate | The query must contain a ParadeDB operator such as `&&&`, `===`, etc.                                                                                            |
| Equi-join key    | The join must contain at least one equality condition such as `a.id = b.id`.                                                                                     |
| Indexed fields   | All join keys, filters, and `ORDER BY` columns must be present in the ParadeDB index. Text and JSON fields must be [columnar](/documentation/indexing/columnar). |
| LIMIT clause     | The query must include a `LIMIT`.                                                                                                                                |

If any checks fail, ParadeDB will emit a `NOTICE` explaining why and fall back to Postgres' native join execution.

To demonstrate, let's create a second table called `orders` that can be joined with `mock_items`:

```sql
CALL paradedb.create_bm25_test_table(
  schema_name => 'public',
  table_name => 'orders',
  table_type => 'Orders'
);

ALTER TABLE orders
ADD CONSTRAINT foreign_key_product_id
FOREIGN KEY (product_id)
REFERENCES mock_items(id);

CREATE INDEX orders_idx ON orders
USING paradedb (order_id, product_id, order_quantity, order_total, customer_name)
WITH (key_field = 'order_id');
```

```sql
SELECT * FROM orders ORDER BY order_id LIMIT 3;
```

```csv Expected Response
 order_id | product_id | order_quantity | order_total | customer_name
----------+------------+----------------+-------------+---------------
        1 |          1 |              3 |       99.99 | John Doe
        2 |          2 |              1 |       49.99 | Jane Smith
        3 |          3 |              5 |      249.95 | Alice Johnson
(3 rows)
```

## Supported Join Types

### Inner Join

An inner join returns rows where a matching row exists in both tables according to the join condition.

```sql
SELECT o.order_id, o.customer_name, o.order_total, m.description
FROM orders o
INNER JOIN mock_items m
  ON o.product_id = m.id
WHERE m.description ||| 'keyboard'
AND o.customer_name ||| 'John'
ORDER BY o.order_total DESC
LIMIT 5;
```

```csv Expected Response
 order_id | customer_name | order_total |       description
----------+---------------+-------------+--------------------------
        4 | John Doe      |      501.87 | Plastic Keyboard
        1 | John Doe      |       99.99 | Ergonomic metal keyboard
(2 rows)
```

To verify join pushdown, run `EXPLAIN` on the query and look for a `ParadeDB Join Scan` in the output.

```csv Expected Response
                                                                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.00 rows=5 width=44)
   ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=44)
         Relation Tree: m INNER o
         Join Cond: o.product_id = m.id
         Limit: 5
         Order By: o.order_total desc
         DataFusion Physical Plan:
           : ProjectionExec: expr=[NULL as col_1, NULL as col_2, order_total@2 as col_3, NULL as col_4, ctid_0@0 as ctid_0, ctid_1@1 as ctid_1]
           :   SortExec: TopK(fetch=5), expr=[order_total@2 DESC], preserve_partitioning=[false]
           :     VisibilityFilterExec: tables=[m, o]
           :       HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(id@1, product_id@1)], projection=[ctid_0@0, ctid_1@2, order_total@4]
           :         CooperativeExec
           :           PgSearchScan: segments=1, query={"with_index":{"query":{"match":{"field":"description","value":"keyboard","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
           :         CooperativeExec
           :           PgSearchScan: segments=1, dynamic_filters=2, query={"with_index":{"query":{"match":{"field":"customer_name","value":"John","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
(15 rows)
```

### Semi Join

A semi join returns rows from the left table when a matching row exists in the right table.
In SQL, this usually appears as an `IN` or `EXISTS` query:

```sql
SELECT o.order_id, o.order_total FROM orders o
WHERE o.product_id IN (
  SELECT m.id
  FROM mock_items m
  WHERE m.description ||| 'keyboard'
)
ORDER BY o.order_total DESC
LIMIT 5;
```

```csv Expected Response
 order_id | order_total
----------+-------------
       27 |      676.15
       57 |      676.15
       11 |      633.94
       41 |      633.94
        4 |      501.87
(5 rows)
```

To verify join pushdown, run `EXPLAIN` on the query and look for a `ParadeDB Join Scan` in the output.

```csv Expected Response
                                                                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.00 rows=5 width=11)
   ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=11)
         Relation Tree: m INNER o
         Join Cond: o.product_id = m.id
         Limit: 5
         Order By: o.order_total desc
         DataFusion Physical Plan:
           : ProjectionExec: expr=[NULL as col_1, order_total@2 as col_2, ctid_0@0 as ctid_0, ctid_1@1 as ctid_1]
           :   SortExec: TopK(fetch=5), expr=[order_total@2 DESC], preserve_partitioning=[false]
           :     VisibilityFilterExec: tables=[m, o]
           :       HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(id@1, product_id@1)], projection=[ctid_0@0, ctid_1@2, order_total@4]
           :         CooperativeExec
           :           PgSearchScan: segments=1, query={"with_index":{"query":{"match":{"field":"description","value":"keyboard","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
           :         CooperativeExec
           :           PgSearchScan: segments=1, dynamic_filters=2, query="all"
(15 rows)
```

### Anti Join

An anti join returns rows from the left table when no matching row exists in the right table. This typically appears as NOT EXISTS or NOT IN.

```sql
SELECT o.order_id, o.order_total FROM orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM mock_items m
  WHERE m.id = o.product_id
    AND m.description ||| 'keyboard'
)
ORDER BY o.order_total DESC
LIMIT 5;
```

```csv Expected Response
 order_id | order_total
----------+-------------
       10 |      638.73
       40 |      638.73
       21 |      632.08
       51 |      632.08
       22 |      605.18
(5 rows)
```

To verify join pushdown, run `EXPLAIN` on the query and look for a `ParadeDB Join Scan` in the output.

```csv Expected Response
                                                                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.00 rows=5 width=11)
   ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=11)
         Relation Tree: o ANTI m
         Join Cond: m.id = o.product_id
         Limit: 5
         Order By: o.order_total desc
         DataFusion Physical Plan:
           : ProjectionExec: expr=[NULL as col_1, order_total@1 as col_2, ctid_0@0 as ctid_0]
           :   SortExec: TopK(fetch=5), expr=[order_total@1 DESC], preserve_partitioning=[false]
           :     VisibilityFilterExec: tables=[o]
           :       HashJoinExec: mode=CollectLeft, join_type=RightAnti, on=[(id@0, product_id@1)], projection=[ctid_0@0, order_total@2]
           :         CooperativeExec
           :           PgSearchScan: segments=1, query={"with_index":{"query":{"match":{"field":"description","value":"keyboard","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
           :         CooperativeExec
           :           PgSearchScan: segments=1, dynamic_filters=1, query="all"
(15 rows)
```

### Left Join

A left join returns all rows from the left table and matching rows from the right table.

```sql
SELECT o.order_id, o.customer_name, o.order_total, m.description
FROM orders o
LEFT JOIN mock_items m
  ON o.product_id = m.id
WHERE o.customer_name ||| 'John'
ORDER BY o.order_total DESC
LIMIT 5;
```

```csv Expected Response
 order_id | customer_name | order_total |       description
----------+---------------+-------------+--------------------------
        4 | John Doe      |      501.87 | Plastic Keyboard
        1 | John Doe      |       99.99 | Ergonomic metal keyboard
(2 rows)
```

To verify join pushdown, run `EXPLAIN` on the query and look for a `ParadeDB Join Scan` in the output.

```csv Expected Response
                                                                                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.00 rows=5 width=44)
   ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=44)
         Relation Tree: m RIGHT o
         Join Cond: o.product_id = m.id
         Limit: 5
         Order By: o.order_total desc
         DataFusion Physical Plan:
           : ProjectionExec: expr=[NULL as col_1, NULL as col_2, order_total@2 as col_3, NULL as col_4, ctid_0@0 as ctid_0, ctid_1@1 as ctid_1]
           :   SortExec: TopK(fetch=5), expr=[order_total@2 DESC], preserve_partitioning=[false]
           :     VisibilityFilterExec: tables=[o]
           :       HashJoinExec: mode=CollectLeft, join_type=Right, on=[(id@1, product_id@1)], projection=[ctid_0@0, ctid_1@2, order_total@4]
           :         VisibilityFilterExec: tables=[m]
           :           CooperativeExec
           :             PgSearchScan: segments=1, query="all"
           :         CooperativeExec
           :           PgSearchScan: segments=1, dynamic_filters=1, query={"with_index":{"query":{"match":{"field":"customer_name","value":"John","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
(16 rows)
```

### Right Join

A right join returns all rows from the right table and matching rows from the left table.

```sql
SELECT o.order_id, o.customer_name, o.order_total, m.description
FROM orders o
RIGHT JOIN mock_items m
  ON o.product_id = m.id
WHERE m.description ||| 'book'
ORDER BY o.order_total DESC
LIMIT 5;
```

```csv Expected Response
 order_id | customer_name | order_total |        description
----------+---------------+-------------+---------------------------
          |               |             | Historical fiction book
       21 | Jada Smith    |      632.08 | Hardcover book on history
       51 | Jada Smith    |      632.08 | Hardcover book on history
        5 | Jane Smith    |      361.38 | Hardcover book on history
(4 rows)
```

To verify join pushdown, run `EXPLAIN` on the query and look for a `ParadeDB Join Scan` in the output.

```csv Expected Response
                                                                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.05 rows=5 width=44)
   ->  Result  (cost=10.00..11.05 rows=5 width=44)
         ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=44)
               Relation Tree: o RIGHT m
               Join Cond: o.product_id = m.id
               Limit: 5
               Order By: o.order_total desc
               DataFusion Physical Plan:
                 : ProjectionExec: expr=[NULL as col_1, NULL as col_2, NULL as col_3, order_total@1 as col_4, ctid_0@0 as ctid_0, ctid_1@2 as ctid_1]
                 :   SortExec: TopK(fetch=5), expr=[order_total@1 DESC], preserve_partitioning=[false]
                 :     VisibilityFilterExec: tables=[m]
                 :       HashJoinExec: mode=CollectLeft, join_type=Right, on=[(product_id@1, id@1)], projection=[ctid_0@0, order_total@2, ctid_1@3]
                 :         VisibilityFilterExec: tables=[o]
                 :           CooperativeExec
                 :             PgSearchScan: segments=1, query="all"
                 :         CooperativeExec
                 :           PgSearchScan: segments=1, query={"with_index":{"query":{"match":{"field":"description","value":"book","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
(17 rows)
```

### Full Join

A full join returns all rows when there is a match in either the left or the right table.

```sql
SELECT o.order_id, o.customer_name, o.order_total, m.description
FROM orders o
FULL JOIN mock_items m
  ON o.product_id = m.id
WHERE (m.description ||| 'book' OR o.customer_name ||| 'John')
ORDER BY o.order_total DESC
LIMIT 5;
```

```csv Expected Response
 order_id | customer_name | order_total |        description
----------+---------------+-------------+---------------------------
          |               |             | Historical fiction book
       51 | Jada Smith    |      632.08 | Hardcover book on history
       21 | Jada Smith    |      632.08 | Hardcover book on history
        4 | John Doe      |      501.87 | Plastic Keyboard
        5 | Jane Smith    |      361.38 | Hardcover book on history
(5 rows)
```

To verify join pushdown, run `EXPLAIN` on the query and look for a `ParadeDB Join Scan` in the output.

```csv Expected Response
                                                                                                                                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.00 rows=5 width=44)
   ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=44)
         Relation Tree: m FULL o
         Join Cond: o.product_id = m.id
         Join Predicate: (m:{"with_index":{"query":{"match":{"field":"description","value":"book","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}} OR o:{"with_index":{"query":{"match":{"field":"customer_name","value":"John","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}})
         Limit: 5
         Order By: o.order_total desc
         DataFusion Physical Plan:
           : ProjectionExec: expr=[NULL as col_1, NULL as col_2, order_total@2 as col_3, NULL as col_4, ctid_0@0 as ctid_0, ctid_1@1 as ctid_1]
           :   SortExec: TopK(fetch=5), expr=[order_total@2 DESC], preserve_partitioning=[false]
           :     FilterExec: pdb_search_predicate(ctid_0@0) OR pdb_search_predicate(ctid_1@1)
           :       HashJoinExec: mode=CollectLeft, join_type=Full, on=[(id@1, product_id@1)], projection=[ctid_0@0, ctid_1@2, order_total@4]
           :         VisibilityFilterExec: tables=[m]
           :           CooperativeExec
           :             PgSearchScan: segments=1, query="all"
           :         VisibilityFilterExec: tables=[o]
           :           CooperativeExec
           :             PgSearchScan: segments=1, query="all"
(18 rows)
```

## Performance

If your join query isn't as fast as you'd like, we invite you to [open a Github issue](https://github.com/paradedb/paradedb/issues).
