> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Joins

> Combining rows from more than one table, including across namespaces.

**Applies to:** Embedded ❌, Enterprise ✅

Use SQL joins to combine rows from two or more tables.

## The join types

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT d.title, c.name
FROM documents AS d
JOIN categories AS c ON c.id = d.category_id
```

| | What it keeps |
| - | - |
| `[INNER] JOIN` | rows matching on both sides |
| `LEFT [OUTER] JOIN` | every left row, nulls where the right has no match |
| `RIGHT [OUTER] JOIN` | every right row |
| `FULL [OUTER] JOIN` | every row from both |
| `CROSS JOIN` | every combination (the product of the two) |

`USING (col)` is shorthand for an equality join on a shared column name, and
avoids repeating it in the select list.

## Across namespaces

Tables in different [namespaces](https://docs.lancedb.com/management/namespace.md) join like any other table, each
addressed by its full `database.schema.table` identifier. The catalog is one
catalog, so there is nothing to federate:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT c.id, u.email
FROM prod.events.clicks AS c
JOIN prod.reference.users AS u ON u.id = c.user_id
```

## Joining a search result

[`fts()`](https://docs.lancedb.com/sql/dql/fts) is a table function, so its result is a relation
and joins like any other. This example assumes `my_docs` has `text` and
`category_id` columns, a full-text index on `text`, and a `categories` table
with `id` and `name` columns:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT c.name, count(*) AS hits
FROM fts(
    'my_docs',
    '{"match":{"column":"text","terms":"puppy"}}'
) AS m
JOIN categories AS c ON c.id = m.category_id
GROUP BY c.name
ORDER BY hits DESC
```

That composition is the reason full-text search is a relation rather than a
predicate: a `WHERE` clause has nowhere to put the relevance score.

## Performance

A [scalar index](https://docs.lancedb.com/indexing/scalar-index.md) can accelerate supported filters on
the input tables. An index on a join key does not by itself guarantee an
index lookup join. Use SQL [`EXPLAIN`](https://docs.lancedb.com/sql/dql/explain) to inspect the scans,
filters, and join chosen for the complete statement.

> [!WARNING]
>
> Avoid selecting blob or vector columns you do not need on either side of a join.
> The join multiplies rows before projection is applied in some plans, so a wide
> column can be materialized many more times than the result has rows.

## Late materialization for wide columns

The planner can avoid the multiplication above: when a join narrows down to
a key column before a wide one is needed, it can defer reading a BlobV2 or
vector column until after the join has already settled which rows survive.
See [Blob Late Materialization](https://docs.lancedb.com/curation/blob-late-materialization.md) for
the eligibility conditions, the `RemoteTake` operator, and how to confirm
a specific query got the deferred read with [`EXPLAIN`](https://docs.lancedb.com/sql/dql/explain).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.