ksqlDB Joins on Confluent Platform

ksqlDB joins enable you to combine data from two or more streams or tables, similar to joins in traditional SQL databases. The result of a join is a new stream or table containing the combined data. ksqlDB handles the low-level logic, allowing you to focus on the business logic of combining your data.

ksqlDB supports these join types:

  • Stream-Stream Joins: ksqlDB supports INNER, LEFT OUTER (often shortened to LEFT JOIN), RIGHT OUTER, and FULL OUTER (often shortened to OUTER JOIN) joins between streams. These joins use windowing to handle the continuous nature of streams. A window defines a period of time during which records are considered for joining. Each record has a grace period, allowing for out-of-order arrival. Windows are tracked per record key, and older records are purged after the grace period. For more information, see Time and Windows.
  • Stream-Table Joins: ksqlDB supports INNER and LEFT joins between a stream and a table. OUTER joins are not supported for stream-table joins. These joins act as lookups against the table, enriching the stream’s data with information from the table. Stream-table joins are always non-windowed.
  • Table-Table Joins: ksqlDB supports primary-key (1:1) and foreign-key (1:N) joins between tables, but not many-to-many (N:M) joins. For foreign-key joins, any column from the left table can be used in the join condition with the primary key of the right table. INNER, LEFT OUTER, and FULL OUTER joins are supported for primary-key joins. While N-way joins are generally supported, FULL OUTER N-way table joins require multiple separate queries, each performing a single FULL OUTER join.

The following SQL shows a stream-table join.

CREATE STREAM pageviews_enriched AS
SELECT users.userid AS userid, pageid, regionid, gender
FROM pageviews
LEFT JOIN users ON pageviews.userid = users.userid
EMIT CHANGES;