Every event that flows through a streaming pipeline is incomplete on its own. An order event carries a product ID but not the product name. A clickstream record has a user ID but no subscription tier. A sensor reading knows its device ID but not its physical location. To make streaming data useful, you need to combine it with context that lives somewhere else.
This is the core problem that stream-table joins solve. A stream-table join enriches a continuous flow of events with reference data stored in a table, producing a richer output stream in real time. Unlike batch ETL, where you can afford to wait hours, stream-table joins operate continuously: every new event gets enriched the moment it arrives.
In this article, you will learn three distinct join patterns for streaming SQL: temporal joins for process-time lookups, regular streaming joins for fully synchronized enrichment, and interval joins for correlating two event streams within a time window. Every SQL example runs on RisingWave, an open-source streaming database, and has been verified against RisingWave 2.8.0.
Why Stream-Table Joins Matter
In traditional databases, a JOIN is a point-in-time operation. You run a query, it scans both tables, matches rows, and returns results. In stream processing, the rules change fundamentally.
Streams are unbounded. New events arrive continuously. The dimension data you are joining against can change over time: a product price gets updated, a user changes their subscription plan, a sensor gets recalibrated. The question becomes: when an event arrives, which version of the dimension data should it see?
This question leads to different join semantics:
- Process-time temporal join: Look up the dimension table at the moment the event is processed. Simple, efficient, and stateless on the stream side.
- Regular streaming join: Maintain full state for both sides. When either side changes, recompute the result. Correct but resource-intensive.
- Interval join: Correlate two event streams where matches must fall within a time window. Used for stream-to-stream enrichment rather than stream-to-table.
Each pattern fits different use cases. Choosing the wrong one leads to either incorrect results or unnecessary resource consumption.
Temporal Joins: Process-Time Lookups
A temporal join is the most common pattern for enriching streaming events with dimension data. It joins a stream (the left side) against a table or materialized view (the right side) using the current state of the table at the time the event is processed.
The key characteristic: updates to the right-side table do not retroactively affect previously joined results. If a product price changes from $29.99 to $34.99, orders that were already enriched with the old price keep their original values. Only new orders see the updated price.
Syntax
RisingWave uses the SQL:2011 standard FOR SYSTEM_TIME AS OF PROCTIME() clause:
SELECT ...
FROM stream_table
[LEFT | INNER] JOIN dimension_table
FOR SYSTEM_TIME AS OF PROCTIME()
ON stream_table.key = dimension_table.key;
The right-side table must have a primary key, and the join condition must include the primary key columns.
Example: Enriching Orders with Product Data
Suppose you have an orders stream and a products dimension table. Each order references a product by ID, and you want to add the product name, category, and price to every order as it arrives.
First, create the tables:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR,
category VARCHAR,
price NUMERIC
);
CREATE TABLE orders (
order_id INT,
product_id INT,
customer_id INT,
quantity INT,
order_time TIMESTAMPTZ
);
Populate the dimension table:
INSERT INTO products VALUES
(1, 'Wireless Mouse', 'Electronics', 29.99),
(2, 'USB-C Cable', 'Electronics', 12.99),
(3, 'Notebook', 'Office Supplies', 4.99),
(4, 'Desk Lamp', 'Furniture', 45.00),
(5, 'Mechanical Keyboard', 'Electronics', 89.99);
Insert some order events:
INSERT INTO orders VALUES
(1001, 1, 501, 2, '2026-04-01 10:00:00+00'),
(1002, 3, 502, 10, '2026-04-01 10:05:00+00'),
(1003, 2, 501, 1, '2026-04-01 10:10:00+00'),
(1004, 5, 503, 1, '2026-04-01 10:15:00+00'),
(1005, 4, 504, 3, '2026-04-01 10:20:00+00');
Now create a materialized view that continuously enriches orders using a temporal join:
CREATE MATERIALIZED VIEW enriched_orders AS
SELECT
o.order_id,
o.customer_id,
p.product_name,
p.category,
o.quantity,
p.price,
o.quantity * p.price AS total_amount,
o.order_time
FROM orders o
JOIN products FOR SYSTEM_TIME AS OF PROCTIME() p
ON o.product_id = p.product_id;
Query the result:
SELECT * FROM enriched_orders ORDER BY order_id;
order_id | customer_id | product_name | category | quantity | price | total_amount | order_time
----------+-------------+---------------------+-----------------+----------+-------+--------------+---------------------------
1001 | 501 | Wireless Mouse | Electronics | 2 | 29.99 | 59.98 | 2026-04-01 10:00:00+00:00
1002 | 502 | Notebook | Office Supplies | 10 | 4.99 | 49.90 | 2026-04-01 10:05:00+00:00
1003 | 501 | USB-C Cable | Electronics | 1 | 12.99 | 12.99 | 2026-04-01 10:10:00+00:00
1004 | 503 | Mechanical Keyboard | Electronics | 1 | 89.99 | 89.99 | 2026-04-01 10:15:00+00:00
1005 | 504 | Desk Lamp | Furniture | 3 | 45.00 | 135.00 | 2026-04-01 10:20:00+00:00
Every order now carries its product context. This materialized view updates incrementally: as new orders arrive, they are enriched and appended automatically.
When to Use Temporal Joins
Temporal joins are the right choice when:
- Your dimension data changes slowly (product catalogs, user profiles, configuration tables)
- You do not need to retroactively update previously enriched events when the dimension changes
- You want to minimize state and compute overhead
- You are building real-time dashboards, alerting pipelines, or event enrichment layers
Left Temporal Joins: Handling Missing Lookups
In production, not every event matches a dimension record. A page view might come from an anonymous user. A transaction might reference a product ID that has not been added to the catalog yet. Using an INNER temporal join silently drops these events. A LEFT temporal join preserves them with NULL values for the dimension columns.
Example: Page View Enrichment
CREATE TABLE app_users (
user_id INT PRIMARY KEY,
username VARCHAR,
country VARCHAR,
subscription_tier VARCHAR
);
CREATE TABLE page_views (
view_id INT,
user_id INT,
page_url VARCHAR,
view_time TIMESTAMPTZ
);
INSERT INTO app_users VALUES
(501, 'alice', 'US', 'premium'),
(502, 'bob', 'UK', 'free'),
(503, 'carol', 'DE', 'premium'),
(504, 'dave', 'JP', 'enterprise');
INSERT INTO page_views VALUES
(1, 501, '/dashboard', '2026-04-01 12:00:00+00'),
(2, 502, '/pricing', '2026-04-01 12:01:00+00'),
(3, 999, '/signup', '2026-04-01 12:02:00+00'),
(4, 503, '/docs/joins', '2026-04-01 12:03:00+00');
Create the enrichment view with a LEFT join:
CREATE MATERIALIZED VIEW enriched_page_views AS
SELECT
pv.view_id,
pv.user_id,
pv.page_url,
pv.view_time,
u.username,
u.country,
u.subscription_tier
FROM page_views pv
LEFT JOIN app_users FOR SYSTEM_TIME AS OF PROCTIME() u
ON pv.user_id = u.user_id;
SELECT * FROM enriched_page_views ORDER BY view_id;
view_id | user_id | page_url | view_time | username | country | subscription_tier
---------+---------+-------------+---------------------------+----------+---------+-------------------
1 | 501 | /dashboard | 2026-04-01 12:00:00+00:00 | alice | US | premium
2 | 502 | /pricing | 2026-04-01 12:01:00+00:00 | bob | UK | free
3 | 999 | /signup | 2026-04-01 12:02:00+00:00 | | |
4 | 503 | /docs/joins | 2026-04-01 12:03:00+00:00 | carol | DE | premium
Notice row 3: user 999 does not exist in the app_users table, so the dimension columns are NULL. The event is preserved, which is critical for analytics. You can later filter or flag these unmatched events downstream.
Building Aggregations on Top of Temporal Joins
Temporal joins become even more powerful when you chain them with aggregations. A common pattern is to enrich events first, then aggregate the enriched results into business metrics.
Example: Revenue by Product Category
CREATE MATERIALIZED VIEW order_totals_by_category AS
SELECT
p.category,
COUNT(*) AS order_count,
SUM(o.quantity * p.price) AS total_revenue
FROM orders o
JOIN products FOR SYSTEM_TIME AS OF PROCTIME() p
ON o.product_id = p.product_id
GROUP BY p.category;
SELECT * FROM order_totals_by_category ORDER BY total_revenue DESC;
category | order_count | total_revenue
-----------------+-------------+---------------
Electronics | 3 | 162.96
Furniture | 1 | 135.00
Office Supplies | 1 | 49.90
This view updates incrementally. As new orders arrive, the revenue totals adjust in real time. Because the temporal join does not maintain stream-side state, the overall resource footprint stays small even as order volume grows.
Interval Joins: Correlating Two Event Streams
Not all enrichment involves a static dimension table. Sometimes you need to correlate events from two different streams based on a time proximity constraint. This is where interval joins come in.
An interval join matches rows from two streams where the timestamps fall within a specified range. Both sides are dynamic, both sides have watermarks, and the system automatically cleans up expired state as the watermarks advance.
Example: Matching Buy and Sell Orders
Consider a trading system where buy orders and sell orders arrive on separate streams. You want to find potential matches: buy and sell orders for the same stock symbol that arrive within one minute of each other.
CREATE TABLE buy_orders (
order_id INT,
symbol VARCHAR,
price NUMERIC,
quantity INT,
order_time TIMESTAMPTZ,
WATERMARK FOR order_time AS order_time - INTERVAL '5' SECOND
) APPEND ONLY;
CREATE TABLE sell_orders (
order_id INT,
symbol VARCHAR,
price NUMERIC,
quantity INT,
order_time TIMESTAMPTZ,
WATERMARK FOR order_time AS order_time - INTERVAL '5' SECOND
) APPEND ONLY;
Note the WATERMARK clause on both tables. Watermarks tell RisingWave how far behind events can arrive (here, up to 5 seconds late). The APPEND ONLY modifier is required for tables with watermarks in RisingWave, indicating that rows are only inserted, never updated or deleted.
Insert some trading data:
INSERT INTO buy_orders VALUES
(1, 'AAPL', 150.00, 100, '2026-04-01 09:30:00+00'),
(2, 'GOOG', 2800.00, 10, '2026-04-01 09:30:30+00'),
(3, 'AAPL', 150.50, 50, '2026-04-01 09:31:00+00');
INSERT INTO sell_orders VALUES
(101, 'AAPL', 150.25, 80, '2026-04-01 09:30:15+00'),
(102, 'GOOG', 2801.00, 15, '2026-04-01 09:30:45+00'),
(103, 'AAPL', 149.75, 120, '2026-04-01 09:31:30+00');
Create the interval join:
CREATE MATERIALIZED VIEW matched_trades AS
SELECT
b.order_id AS buy_id,
s.order_id AS sell_id,
b.symbol,
b.price AS buy_price,
s.price AS sell_price,
s.price - b.price AS spread,
LEAST(b.quantity, s.quantity) AS matched_quantity,
b.order_time AS buy_time,
s.order_time AS sell_time
FROM buy_orders b
JOIN sell_orders s
ON b.symbol = s.symbol
AND b.order_time BETWEEN s.order_time - INTERVAL '1' MINUTE
AND s.order_time + INTERVAL '1' MINUTE;
SELECT * FROM matched_trades ORDER BY buy_id, sell_id;
buy_id | sell_id | symbol | buy_price | sell_price | spread | matched_quantity | buy_time | sell_time
--------+---------+--------+-----------+------------+--------+------------------+---------------------------+---------------------------
1 | 101 | AAPL | 150.00 | 150.25 | 0.25 | 80 | 2026-04-01 09:30:00+00:00 | 2026-04-01 09:30:15+00:00
2 | 102 | GOOG | 2800.00 | 2801.00 | 1.00 | 10 | 2026-04-01 09:30:30+00:00 | 2026-04-01 09:30:45+00:00
3 | 101 | AAPL | 150.50 | 150.25 | -0.25 | 50 | 2026-04-01 09:31:00+00:00 | 2026-04-01 09:30:15+00:00
3 | 103 | AAPL | 150.50 | 149.75 | -0.75 | 50 | 2026-04-01 09:31:00+00:00 | 2026-04-01 09:31:30+00:00
The interval join found four matches. Buy order 1 (AAPL at $150.00) matched with sell order 101 (AAPL at $150.25) because they arrived 15 seconds apart. Buy order 3 matched with two sell orders because both fell within the one-minute window.
The key advantage of interval joins is bounded state. Because of the time constraint and watermarks, RisingWave automatically discards events that can no longer match, keeping memory consumption predictable.
Choosing the Right Join Pattern
| Criteria | Temporal Join | Regular Join | Interval Join |
| Left side | Stream (events) | Stream or table | Stream with watermarks |
| Right side | Table with primary key | Stream or table | Stream with watermarks |
| State management | No stream-side state | Full state for both sides | Bounded by time window |
| Retroactive updates | No | Yes | No |
| Use case | Event enrichment | Bidirectional sync | Stream-stream correlation |
| Resource cost | Low | High | Medium |
Decision guide
Enriching events with slowly changing dimensions? Use a temporal join. Products, users, configuration data - these change infrequently, and you rarely need retroactive corrections.
Need results to update when either side changes? Use a regular join. This is appropriate for dashboards that should reflect the latest dimension values for all historical events.
Correlating events from two independent streams? Use an interval join with appropriate time bounds. Trading, IoT sensor fusion, click-conversion attribution - anywhere two event types need to be matched by proximity.
Performance Considerations for Streaming Joins
Streaming joins can consume significant resources if not designed carefully. Here are practical guidelines:
Temporal joins are your first choice for enrichment. They avoid maintaining a separate copy of the stream-side state. In RisingWave, the lookup goes directly against the dimension table's storage, which means the join does not double your state footprint. For high-volume streams with stable dimension tables, this difference is substantial.
Interval joins need tight time bounds. The wider your interval window, the more state both sides must retain. A one-minute window on a stream producing 10,000 events per second means up to 600,000 events buffered per side. Set the interval as narrow as your use case allows.
Primary keys on dimension tables enable index lookups. When you declare a primary key on the right-side table in a temporal join, RisingWave uses an index lookup instead of a full scan. Always define primary keys on your dimension tables.
Filter early. If your stream contains events you do not need to enrich (for example, heartbeat events or internal system events), filter them out with a WHERE clause before the join. This reduces the number of lookups the temporal join must perform.
For more details on optimizing streaming queries, see the RisingWave performance tuning documentation.
What Is a Stream-Table Join in SQL?
A stream-table join is a continuous join operation where one input is an unbounded stream of events and the other is a table containing reference or dimension data. Each new event in the stream triggers a lookup against the table, enriching the event with additional context. In RisingWave, this is implemented through the temporal join syntax using FOR SYSTEM_TIME AS OF PROCTIME(). The stream side provides the driving input, while the table side serves as the lookup target. Unlike batch joins, a stream-table join operates incrementally - each event is processed individually as it arrives rather than scanning the entire dataset.
How Do Temporal Joins Differ from Regular Joins in Stream Processing?
Temporal joins and regular joins differ in how they handle updates to the dimension (right) side. A regular streaming join maintains full state for both inputs and recomputes results when either side changes. If a product price updates from $29.99 to $34.99, a regular join retroactively updates all enriched orders that referenced that product. A temporal join, by contrast, only looks up the dimension table when a new event arrives on the stream side. Previous results remain unchanged. This makes temporal joins more efficient (less state, fewer recomputations) but means your enriched data reflects the dimension values at processing time, not necessarily the latest values. Choose temporal joins when retroactive consistency is not required, and regular joins when it is.
When Should You Use an Interval Join Instead of a Temporal Join?
Use an interval join when both sides of the join are event streams rather than a stream and a static table. Interval joins correlate events from two streams that occur within a defined time window. Common use cases include matching ad clicks with conversions, correlating buy and sell orders in trading systems, and joining sensor readings from co-located devices. The time bound is critical: it determines how much state the system retains. Temporal joins are not suitable for stream-stream scenarios because they require one side to be a table with a primary key serving as the lookup target.
Can You Chain Multiple Joins in a Single Streaming Query?
Yes. RisingWave supports multi-way joins in materialized views. You can chain a temporal join for enrichment with a regular join for additional context, or combine multiple temporal joins to enrich an event from several dimension tables. For example, you might enrich an order event with both product details and customer details in a single query by joining the orders stream against both a products table and a customers table using separate FOR SYSTEM_TIME AS OF PROCTIME() clauses. Each additional join adds lookup overhead, so monitor query performance as you add join stages. See the RisingWave join documentation for the full syntax reference.
Conclusion
Stream-table joins are a foundational building block for any real-time data pipeline. The three patterns covered here address the most common scenarios:
- Temporal joins enrich streaming events with dimension data using a stateless, process-time lookup. They are the most efficient option for slowly changing dimensions.
- Left temporal joins preserve events that do not match any dimension record, which is critical for complete analytics.
- Interval joins correlate two event streams within a time window, with automatic state cleanup via watermarks.
- Aggregations on top of temporal joins let you build real-time business metrics from enriched data.
The SQL examples in this article run directly on RisingWave using standard SQL syntax. You do not need to learn a new framework or API - if you know SQL JOINs, you already know how to build streaming enrichment pipelines.
Ready to try stream-table joins yourself? Get started with RisingWave in 5 minutes. Quickstart
Join our Slack community to ask questions and connect with other stream processing developers.
