---
title: Supported Join Types
description: JOIN types and pushdown requirements supported by ParadeDB
canonical: https://www.paradedb.com/docs/reference/joins/overview
---
ParadeDB supports all standard Postgres `JOIN` types, including:
- `INNER JOIN`
- `LEFT JOIN`
- `RIGHT JOIN`
- `FULL JOIN`
- `SEMI JOIN`
- `ANTI JOIN`
- `CROSS JOIN`
ParadeDB optimizes `JOIN` queries involving search operations through **join pushdown**, focusing on row-reducing ("above-the-fold") workloads such as Top K result sets and [aggregates](/reference/aggregates/limitations#join-support). When join pushdown applies, ParadeDB evaluates search predicates, joins, sorting, limits, or aggregations on index data before touching the Postgres heap.
If a query does not meet the pushdown requirements, ParadeDB falls back to Postgres'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 fetching surviving table rows. This page focuses on row-returning Top K joins; for `GROUP BY`, SQL aggregates, and `pdb.agg` across joined tables (which do not require a `LIMIT`), see [Aggregates over Joins](/reference/aggregates/limitations#join-support). For guidance on when to join normalized tables versus denormalizing, see [Schema Modeling](/reference/indexing/schema-modeling).
To disable join pushdown for debugging, run `SET
paradedb.enable_join_custom_scan TO off;`.
## Requirements for Join Pushdown
Join pushdown is automatically used when a query meets the following conditions (including queries with non-equi join conditions, `SELECT DISTINCT`, `LATERAL unnest(...)` on indexed array columns, or [`pdb.agg(...) OVER ()`](/reference/aggregates/facets) window facets). If any check fails, Postgres executes 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 `&&&`, `===`, or a vector distance `ORDER BY` (`<->`, `<=>`, `<#>`). |
| Indexed fields | All join conditions, filters, `SELECT DISTINCT`, and `ORDER BY` columns must be present in the ParadeDB index. Text and JSON fields must be [columnar](/reference/indexing/columnar). |
| Ordering | Optional `ORDER BY` on indexed columns or [expressions](/reference/indexing/indexing-expressions), [`pdb.score(...)`](/reference/full-text/score#joined-scores) (including positive `+` and `*` combinations; see [Score-Ordered Joins](#score-ordered-joins)), or vector distance (`<->`, `<=>`, `<#>`). |
| LIMIT clause | Row-returning joins must include a `LIMIT` ([aggregate joins](/reference/aggregates/limitations#join-support) do not require a `LIMIT`). |
If any checks fail (including unsupported `ORDER BY` shapes), ParadeDB emits a `WARNING` controlled by `paradedb.planner_warnings` explaining why and falls back to Postgres' native join execution. Vector distance `ORDER BY` currently requires ascending order on `INNER` or `SEMI` joins and excludes rows with a `NULL` vector (matching Base Scan behavior).
To demonstrate, let's create a second table called `orders` that can be joined with `mock_items`:
```sql
CALL paradedb.create_paradedb_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);
```
```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)
```
## Score-Ordered Joins
Join pushdown supports ordering by [`pdb.score`](/reference/full-text/score#joined-scores) from a single table or by a monotonic combination of scores across tables using `+`, `*`, and positive constants:
```sql
SELECT
o.order_id,
o.customer_name,
m.description,
2.0 * pdb.score(m.id) + pdb.score(o.order_id) AS score
FROM orders o
INNER JOIN mock_items m
ON o.product_id = m.id
WHERE o.customer_name ||| 'Johnson'
OR m.description ||| 'running shoes'
ORDER BY score DESC, o.order_id
LIMIT 5;
```
Each `pdb.score(...)` call computes a BM25 score against that table's index, and `+` sums the per-table scores in the same way a single-table BM25 query sums its clauses (unmatched `OR` branches contribute `0.0`). See [How Schema Shape Affects BM25 Scoring](/reference/indexing/schema-modeling#how-schema-shape-affects-bm25-scoring) for how corpus statistics behave across tables.
Inside `ParadeDB Join Scan`, ParadeDB chooses between two execution strategies for score-ordered Top K joins:
- `HashJoinExec` + `SortExec`: Used on smaller inputs (below [`paradedb.rank_join_min_rows`](/reference/configuration#query-planning), `10000` estimated matching rows by default) or when scanning and joining in one pass is cheaper, such as two-sided conjunctive (`AND`) joins.
- `RankJoinExec`: Pulls top-scoring rows incrementally and probes the other side on demand so the join can stop once the Top K scores are established, without scoring every matching row.
`RankJoinExec` applies when all of the following hold:
- The join is an `INNER JOIN` (or a `SEMI JOIN` ordered by the outer table's score).
- `ORDER BY` sorts descending on `pdb.score(...)` or a positive combination built from `+`, `*`, and positive constants. For cross-table `OR` predicates, the score expression must include a positive additive term for every `OR` branch.
- `LIMIT` is `1000` or less.
On small test tables such as `orders` and `mock_items`, run `SET
paradedb.rank_join_min_rows = 0;` before `EXPLAIN` if you want to inspect a
`RankJoinExec` plan directly. When `RankJoinExec` is not eligible, `EXPLAIN`
emits a `RankJoinExec not used` diagnostic controlled by
`paradedb.planner_warnings`.
## Partitioned Joins (Beta)
Partitioned joins are currently a Beta feature under active development.
When both tables in an equi-join are indexed using [`partition_by`](/reference/indexing/partition-by) on their join keys, ParadeDB automatically joins matching partition ranges locally on each worker without exchanging data between processes.
See the [Partitioning Guide](/reference/indexing/partition-by) for setup and column selection advice.
## 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).