What are the join types and co-partitioning requirements in ksqlDB, and how do stream-stream, stream-table, and table-table joins differ?

## Joining streams of data A **join** combines records from two collections on a join key. Because ksqlDB operates on unbounded data, *what 'match' means* depends on whether each side is a STREAM (events) or a TABLE (current state). ### Co-partitioning: the universal prerequisite Kafka data is split into **partitions**, and a given key always hashes to the same partition. For a join to find matching records on the **same node/partition**, both inputs must be **co-partitioned**: 1. **Same join key** — joined on the column that is each side's key. 2. **Same number of partitions** on both topics. 3. **Same partitioning strategy** (same partitioner/hash) so equal keys land on equal partition numbers. If you join on a **non-key** column, ksqlDB transparently inserts a **repartition** (an internal topic re-keyed by the join column) so co-partitioning holds. If partition counts differ, you must repartition one side yourself. Violating co-partitioning silently produces missing matches — a classic gotcha. ### Stream-stream join — windowed Both sides are unbounded event streams, so 'match' must be bounded in **time**. You supply a `WITHIN` clause: ```sql SELECT * FROM orders o JOIN shipments s WITHIN 1 HOUR ON o.order_id = s.order_id EMIT CHANGES; ``` An order matches a shipment if their **timestamps are within 1 hour** of each other. ksqlDB buffers each side's recent records in **windowed state stores**. Output is a **STREAM** of joined events. Supports INNER/LEFT/FULL OUTER. Use for correlating two event flows (e.g., request/response, click/conversion). ### Stream-table join — enrichment (not windowed) Here one side is the **current state**. Each **stream** event triggers a lookup of the **latest** value for that key in the **table**: ```sql SELECT * FROM clicks c JOIN users u ON c.user_id = u.user_id EMIT CHANGES; ``` This is a **temporal/as-of** join: the click is enriched with the user's value *as it was at processing time*. It fires only on the **stream** side — a table update does **not** retroactively re-emit past stream events. Supports INNER and LEFT (no FULL OUTER). This is the bread-and-butter pattern for enriching events with reference/dimension data. ### Table-table join — materialized Both sides are evolving state. ksqlDB maintains a **continuously-updated joined TABLE**; a change on **either** side re-evaluates and re-emits the joined row: ```sql SELECT * FROM accounts a JOIN profiles p ON a.id = p.id EMIT CHANGES; ``` Supports INNER/LEFT/FULL OUTER. The result is itself materialized and can serve pull queries. Newer ksqlDB also supports **foreign-key (n:1) table-table joins**, which relax the requirement that the join column be the right side's primary key (and don't require identical partition counts the same way). ### Summary of differences | Join | Windowed? | Fires on | Output | Outer support | |------|-----------|----------|--------|---------------| | stream-stream | yes (`WITHIN`) | either side within window | STREAM | INNER/LEFT/FULL | | stream-table | no | stream side only | STREAM | INNER/LEFT | | table-table | no | either side | TABLE | INNER/LEFT/FULL | ### Edge cases - A stream-table join with a **null** table value (or no match) yields no row for INNER, or null-filled columns for LEFT. - Stream-stream windowed state respects a **grace/retention** bound; very old events expire and won't match. - The stream-table join uses the table value **current at the time the stream record is processed**, which depends on relative arrival/timing of the two topics — a common source of 'why didn't it enrich' confusion (table not yet loaded). - Foreign-key table-table joins re-key internally and have their own subscription/response topics.