本页尚未翻译

本页内容仍为英文原文，源自官方文档 sql/dql/join。中文翻译正在分批进行中。

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

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.