Join Types and Restrictions

In order to determine what type of join simple query you want to create in ESP Studio, you must use this reference to determine how components of your join can be attached, and what settings to modify in the Edit Join Expression dialog box.

Note: If you have created a join using comma-separated syntax in the CCL editor, and subsequently added an ON clause using the Edit Join Expression dialog in the Visual editor, the WHERE clause initially created in the comma-separated syntax will not be removed. This does not affect the result, however it will negatively affect performance.

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 policy defined. A join can contain any number of windows and delta streams (with their respective keep policies), 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 policy converts a delta stream into an unnamed window.

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.

Key Field Rules

Key field rules are necessary to ensure that rows are not rejected due to duplicate inserts or due to the key fields being NULL. Because regular streams do not use primary keys, these rules apply only to windows and delta streams.

The key fields of the target are always derived completely from the keys of the many side of the join. In a many-many relationship, the keys are derived from the keys of both sides of the join.
In a one-one relationship, the keys are derived completely from either side of the relationship.
In an outer join, the key fields are derived from the outer side of the join. An error is generated if the outer side of the join is not the many-side of a relationship.
In a full-outer join, the number of key columns and the type of key columns need to be identical in all sources and targets. Also, the key columns require a firstnonnull expression that includes the corresponding key columns in the sources.

When the result of a join is a window, specific rules determine the columns that form the primary key of the target window. In a multitable join, the same rules apply because conceptually each join is produced in pairs, and the result of a join is then joined with another stream or window, and so on.

This table illustrates this information in the context of join types:

	One-One	One-Many	Many-One	Many-Many
INNER	Keys from at least one side should be included in the projection list (or a combination of them if keys are composite).	Keys from the right side should be included in the projection list.	Keys from the left side should be included in the projection list.	Keys from both sides should be included in the projection list.
LEFT	Keys from the left side alone should be included.	Not allowed.	Keys from the left side should be included in the projection list.	Not allowed.
RIGHT	Keys from the right side alone should be included.	Keys from the right side should be included in the projection list.	Not allowed.	Not allowed.
OUTER	Keys should be formed using firstnonnull () on each pair of keys from both sides.	Not allowed.	Not allowed.	Not allowed.

These options can be defined in the Options pane of the Edit Join Expression dialog box.

Nested Joins

Several important functions are necessary to note in Event Stream Processor when implementing a nested join. Nested join syntax is supported in CCL, but you cannot create or edit a nested join in the Visual editor. When a nested join is defined in the CCL file, and you switch to the Visual editor, you see an empty join compartment.