Skip to Content

JOINs: Feature↔Label Joins

When to use

Combine rows from two or more tables — most often to attach a ground-truth label to feature rows before training.

Analogy

Users live on one shelf, events on another. A JOIN walks both shelves in parallel and pairs up rows that share a key — like matching socks.

Data-flow diagram

  users (user_id, country)     events (user_id, ts, action)
       |                              |
       +------ INNER JOIN ON ----------+  -> filtered set
       |                                  only users that acted
       +------ LEFT  JOIN ON ----------+  -> all users, even
       |                                  the ones who did nothing
       +------ FULL  JOIN ON ----------+  -> union difference

Deep explanation

Postgres supports INNER, LEFT, RIGHT, FULL and CROSS joins. INNER keeps only rows that match on both sides; LEFT keeps every row on the left, padding missing right-side columns with NULL — the most common AI pattern (every entity gets a row, even unlabeled ones). Be explicit about join predicates and place them in ON, not WHERE; non-equi joins (range, anti) are valid but the planner hates them — use anti-join patterns like NOT EXISTS when possible. Cardinality matters: a join that explodes 1 million rows to 50 billion is the leading cause of feature-store OOMs.

Examples

Example 1

-- 4a: LEFT JOIN to attach label; missing labels stay NULL
SELECT f.user_id, f.features, l.churned
  FROM user_features AS f
  LEFT JOIN user_labels AS l USING (user_id)
 WHERE f.feature_ts = '2026-01-01';

LEFT JOIN is the AI default: every feature row survives training, even without a label, leaving room for semi-supervised approaches.

Example 2

-- 4b: INNER JOIN when only labeled rows are trainable
SELECT f.*, l.label
  FROM user_features AS f
  INNER JOIN user_labels AS l USING (user_id)
 WHERE l.task = 'churn' AND l.window = '30d';

INNER JOIN prunes the training set down to fully-labeled rows — use when an unlabeled row cannot contribute.

Example 3

-- 4c: anti-join to find unlabeled users (candidates for labeling pipeline)
SELECT u.user_id
  FROM users AS u
 WHERE NOT EXISTS (
       SELECT 1 FROM user_labels l
        WHERE l.user_id = u.user_id
          AND l.task = 'churn'
       );

NOT EXISTS is faster than NOT IN when the right side can be empty; it lets the planner short-circuit per outer row.

Common mistake

Implicit cross joins. Forgetting ON and writing SELECT ... FROM a, b WHERE a.id = b.id looks fine but the planner first builds the cartesian product, then filters — billions of rows. Always use explicit JOIN syntax. Another trap: non-equi joins without indexes; range joins can easily OOM the executor.

Key takeaway

Default to LEFT JOIN when assembling training rows; use INNER to prune to fully-labeled sets; switch to NOT EXISTS for anti-joins; always write ON explicitly and never rely on comma-join syntax.

Production Failure Playbook

Failure scenario 1: join-cardinality-explosion

Failure scenario 2: comma-join-correlated