- Index

< Previous |  | Next >

# Tuple Join

## Syntax

    SELECT target_list
      FROM stream_identifier_1 '[' {window_identifier_1|window_specification} ']' [[AS] stream_alias_1],
           stream_identifier_2 '[' {window_identifier_2|window_specification} ']' [[AS] stream_alias_2]
      WHERE predicate
      [ERROR INTO stream_identifier];

### Substitutable Fields

`target_list`

  One or more entries, separated by commas, of the format target_list_entry.

`target_list_entry`

  A value, of the format

      scalar_expression [[AS] output_field_identifier]

  to be included in the result set returned by the statement. Values can be obtained from a tuple field or from a simple function. Optionally, the name for a value can be modified through an alias.

`scalar_expression`

  A value that will be included in the output tuple. Fields from either input tuple can be included in the output tuple. If desired, the output field can be renamed using an alias.

`output_field_identifier`

  An optional name (alias) for a value in the result set. Alias names must be used for values derived from functions.

`stream_identifier`

  The streams containing the input tuples. Each stream must be windowed; the corresponding window is identified within the required brackets that follow each stream_identifier.

`window_identifier`

  A named window specification previously declared with a CREATE WINDOW statement. Both streams can use the same window specification, or they can be associated with different window specifications. Note that the square braces are a required part of the syntax.

`window_specification`

  A window specification defined within the FROM clause. Both streams can use the same window specification, or they can be associated with different window specifications. Note that the square braces are a required part of the syntax.

`stream_alias`

  An optional alias name for a stream.

`predicate`

  A conditional statement that establishes the constraints for the join, for example, stream_alias_1.field_identifier==stream_alias_2.field_identifier.

### ERROR INTO Clause

You can append an ERROR INTO clause just before the closing semicolon. The StreamSQL ERROR INTO clause is analogous to the Enable Error Output Port check box for operators and adapters in EventFlow applications.

Use ERROR INTO with the name of a stream, which must already exist. This sets up an Error Port for this operator, which is much like a local catch mechanism for errors from this operator.

See Using Error Ports and Error Streams for a discussion of StreamBase error handling mechanisms.

## Discussion

A tuple-based join is a SELECT statement in which the FROM clause includes two windowed stream entries. Within the window specifications, ADVANCE must have the value one. The WHERE clause establishes the constraints that will be used to select tuples for joining.

If the window specification associated with a stream includes a TIMEOUT clause, tuples will be dropped from the stream's buffer after the timeout period has elapsed and will no longer be available for matching.

A tuple-based join generates a stream and can be used anywhere a stream expression is acceptable. As an alternative, the output could be captured in a stream, as illustrated in the following code fragments.

    CREATE [OUTPUT] STREAM stream_identifier;
    SELECT ... INTO stream_identifier;

    CREATE [OUTPUT] STREAM stream_identifier AS
      SELECT ...;

Or, for an OUTPUT STREAM

    SELECT ... => CREATE OUTPUT STREAM stream_identifier;

## Related Topics

- SELECT Statement

- VJOIN Statement

- GATHER Statement

- MERGE Statement

- UNION Statement

Back to Top ^