Enterprise only
Use SQL joins to combine rows from two or more tables.
The join types
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 fulldatabase.schema.table identifier. The catalog is one
catalog, so there is nothing to federate:
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:
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 SQLEXPLAIN to inspect the scans,
filters, and join chosen for the complete statement.
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, theRemoteTake operator, and how to confirm
a specific query got the deferred read with EXPLAIN.