Joining datasets

Applies to: Embedded ❌, Enterprise ✅ A SQL join combines records from two tables using a shared key. Use joins to attach labels, metadata or model scores to your documents. Choose an inner join to keep matching records, or a left join to retain records without a match.

Example data

Connect with an Enterprise SQL client. The examples use a small document table and a separate label table. Expand the setup below to create them.

Create the example tables

curation_documents has six records describing four documents. curation_labels has one label for each of three documents. row_id identifies a record; doc_id identifies the document it describes.Run each statement separately in a scratch database or namespace. Create these tables once for the whole section, using fresh names if they already exist.

CREATE TABLE curation_documents (
    row_id BIGINT, doc_id BIGINT, version BIGINT,
    source VARCHAR, caption VARCHAR, score DOUBLE
);
INSERT INTO curation_documents VALUES
    (1, 10, 1, 'web', NULL, 0.80),
    (2, 10, 2, 'web', 'A cat on a chair', 0.95),
    (3, 20, 1, 'books', 'A dog in a field', 0.90),
    (4, 30, 1, 'web', NULL, 0.70),
    (5, 30, 1, 'web', 'A cat by a window', 0.85),
    (6, 40, 1, 'books', 'A bird in a tree', 0.60);

CREATE TABLE curation_labels (doc_id BIGINT, label VARCHAR);
INSERT INTO curation_labels VALUES
    (10, 'cat'), (20, 'dog'), (30, 'cat');

score is a sample quality score, not a search result score. Document 10 has two versions. Document 30 has two records at the same version. Document 40 has no label. These cases let you check the effect of each operation.

Basic join

An inner join keeps matching records:

SELECT d.row_id, d.doc_id, l.label
FROM curation_documents AS d
JOIN curation_labels AS l ON l.doc_id = d.doc_id
ORDER BY d.row_id;

This returns 5 rows: both records for document 10, one for document 20, and both records for document 30. To produce one row per document, deduplicate the source records.

Find records without a match

A left join keeps every record from the left table and fills missing right-side values with NULL. Add WHERE l.doc_id IS NULL to return only unlabeled documents:

SELECT DISTINCT d.doc_id
FROM curation_documents AS d
LEFT JOIN curation_labels AS l ON l.doc_id = d.doc_id
WHERE l.doc_id IS NULL
ORDER BY d.doc_id;

The result is document 40. You might send it for annotation, retain it for an unlabeled-data workflow, or exclude it from a task that requires labels.

Handle duplicate keys

This example joins curation_documents.doc_id to curation_labels.doc_id. First check whether the label table contains more than one row per document:

SELECT doc_id, count(*) AS labels
FROM curation_labels
GROUP BY doc_id
HAVING count(*) > 1;

The example returns no rows. If your data returns duplicates, decide whether they represent valid multiple labels or records that need consolidation. A join does not enforce one-to-one matching.

Call a Function across the join

A Function can read columns from either side of a join, the same as any other expression — it runs once the join has matched rows, not before. Register caption_quality first:

from lancedb import udf

@udf
def caption_quality(caption: str) -> float:
    ...  # score the caption, e.g. with a lightweight heuristic or model

db.create_function_async(caption_quality).wait()

SELECT d.row_id, l.label, caption_quality(d.caption) AS quality
FROM curation_documents AS d
JOIN curation_labels AS l ON l.doc_id = d.doc_id
WHERE d.caption IS NOT NULL
ORDER BY d.row_id;

Scoring after the join, rather than before, means caption_quality only runs on rows that actually matched a label — a document with no label never pays for the Function call.

Join tables across namespaces

Use a table’s qualified path to address it in another namespace. For example, a table named images in prod.media is addressed as prod.media.images. The same matching-key and join-type rules apply; your identity needs access to the source objects.

Combine search results with metadata

A SQL full-text search returns rows that can be joined to other tables. Join using an identifier retained in the search result, then check whether the metadata has one or several matches per result. For an SDK search, retain the matching IDs and combine them with metadata in your application or save the selected records to a table for further SQL work. See SQL search interfaces for supported query forms.

Join performance

Check the row count, unmatched keys and duplicate matches before using a joined dataset. Select only the columns needed for the next step, especially when source records include large media or embedding columns. Use Query plans to see how the query reads and combines data. Check the plan to see whether the query uses indexes and how it performs the join. An index on a key does not guarantee that the join will use it.

Defer wide columns until after the join

When one side of a join carries a BlobV2 column, an embedding, or both, join on a narrow key first and project the wide columns only in the final select list, so the planner can defer reading them until after the join has already narrowed down which rows survive. See Blob Late Materialization for the mechanism, eligibility, and how to confirm it happened.

Save the result or continue curating

Keep the query for reuse or save the selected data. See Joins for join types and exact SQL behavior.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Joining datasets

Example data

Basic join

Find records without a match

Handle duplicate keys

Call a Function across the join

Join tables across namespaces

Combine search results with metadata

Join performance

Defer wide columns until after the join

Save the result or continue curating

​Example data

​Basic join

​Find records without a match

​Handle duplicate keys

​Call a Function across the join

​Join tables across namespaces

​Combine search results with metadata

​Join performance

​Defer wide columns until after the join

​Save the result or continue curating

Example data

Basic join

Find records without a match

Handle duplicate keys

Call a Function across the join

Join tables across namespaces

Combine search results with metadata

Join performance

Defer wide columns until after the join

Save the result or continue curating