Joins

Applies to: Embedded ❌, Enterprise ✅ Use SQL joins to combine rows from two or more tables.

The join types

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 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:

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() 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:

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 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 to inspect the scans, filters, and join chosen for the complete statement.

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 for the eligibility conditions, the RemoteTake operator, and how to confirm a specific query got the deferred read with EXPLAIN.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

The join types

Across namespaces

Joining a search result

Performance

Late materialization for wide columns

​The join types

​Across namespaces

​Joining a search result

​Performance

​Late materialization for wide columns

The join types

Across namespaces

Joining a search result

Performance

Late materialization for wide columns