Joins

Streams, windows, or delta streams can participate in a join. However, a delta stream can participate in a join only if it has a KEEP clause. A join can contain any number of windows and delta streams (with their respective KEEP clauses), but only one stream. Self joins are also supported. For example, you can include the same window or delta stream more than once in a join, provided each instance has its own alias.

In a stream-window join the target can be a stream or a window with aggregation. Using a window as a target requires an aggregation because the stream-window join does not have keys and a window requires a key. The GROUP BY columns in aggregation automatically forms the key for the target window. This restriction does not apply to delta stream-window joins because use of the KEEP clause converts a delta stream into an unnamed window.

Note: Unnamed windows are implicitly created when using a join with a window that produces a stream. The unnamed window is created to ensure that a join does not see records that have not yet arrived at the join. This can happen because the source to the join and the join itself are running in separate threads.

Joins are performed in pairs but you can combine multiple joins to produce a complex multitable join. Depending on the complexity and nature of the join, the compiler may create intermediate joins. The comma join syntax supports only inner joins, and the WHERE clause in this syntax is optional. When it is omitted, it means that there is a many-many relationship between the streams in the FROM clause.

Joins in ANSI syntax can add the DYNAMIC modifier to a window or stream to indicate that its data changes frequently. A secondary index is created on windows joining with an incomplete primary key of a DYNAMIC window or stream. This improves performance but uses additional memory proportional to the total data length of key columns in the index. By default, windows and streams are STATIC and no secondary indices are created.

Event Stream Processor supports all join types:

Join Type	Syntax	Description
Inner Join	`INNER JOIN`	One record from each side of the join is required for the join to produce a record.
Left Outer Join	`LEFT JOIN`	A record from the left side (outer side) of the join is produced regardless of whether a record exists on the right side (inner side). When a record on the right side does not exist, any column from the inner side has a NULL value.
Right Outer Join	`RIGHT JOIN`	Reverse of left outer join, where the right side is the outer side and the left side is the inner side of the join.
Full Outer Join	`FULL JOIN`	A record is produced whether there is a match on the right side or the left side of the join.

Event Stream Processor also supports these cardinalities:

Type	Description
One-One	Keys of one side of the join are completely mapped to the keys of the other side of the join. One incoming row produces only one row as output.
One-Many	One record from the one side joins with multiple records on the many side. The one side of the join is the side where all the primary keys are mapped to the other side of the join. Whenever a record comes on the one-side of the join, it produces many rows as the output.
Many-Many	The keys of both side of the join are not completely mapped to the keys of the other side of the join. A row arriving on either side of the join has the potential to produce multiple rows as output.

Note: When a join produces multiple rows, the rows are grouped into a single transaction. If the transaction fails, all of the rows are discarded.

This example joins two windows (InStocks and InOptions) using the FROM clause with ANSI syntax. The result is an output window.

CREATE INPUT Window InStocks SCHEMA StocksSchema Primary Key (Ts) ;

CREATE INPUT Window InOptions SCHEMA OptionsSchema Primary Key (Ts) KEEP ALL ;

CREATE Output Window OutStockOption  SCHEMA OutSchema
	Primary Key (Ts) 
	KEEP ALL 
AS 
	SELECT InStocks.Ts Ts, 
		InStocks.Symbol Symbol, 
		InStocks.Price StockPrice, 
		InStocks.Volume StockVolume, 
		InOptions.StockSymbol StockSymbol,
		InOptions.OptionSymbol OptionSymbol, 
		InOptions.Price OptionPrice, 
		InOptions.Volume OptionVolume
	FROM InStocks JOIN InOptions 
	  ON
	    InStocks.Symbol = InOptions.StockSymbol and 
					InStocks.Ts = InOptions.Ts ;