Join Examples: ANSI Syntax

Examples of different join types using the ANSI syntax.

Refer to these inputs for the examples below.

CREATE INPUT STREAM S1 SCHEMA (Val1S1 integer, Val2S1 integer, Val3S1 string);
CREATE INPUT WINDOW W1 SCHEMA (Key1W1 integer, Key2W1 string, Val1W1 integer, Val2W1 string) PRIMARY KEY (Key1W1, Key2W1);
CREATE INPUT WINDOW W2 SCHEMA (Key1W2 integer,  Key2W2 string, Val1W2 integer, Val2W2 string) PRIMARY KEY (Key1W2, Key2W2);
CREATE INPUT WINDOW W2 SCHEMA (Key1W3 integer,  Val1W3 integer, Val2W3 string) PRIMARY KEY (Key1W3);

Simple Inner Join: One-One

Here, keys can be derived from either W1 or W2.

CREATE OUTPUT WINDOW OW1
PRIMARY KEY  (Key1W2, Key2W2) 
SELECT W1.*, W2.*
FROM W1 INNER JOIN W2 ON W1.Key1W1 = W2.Key1W2 AND W1.Key2W1 = W1.Key2W2

Simple Left Join: One-One

The keys are derived from the outer side of the left join. It is incorrect to derive the keys from the inner side because the values could be null.

CREATE OUTPUT WINDOW OW1
PRIMARY KEY (Key1W1, Key2W1) 
SELECT W1.*, W2.*
FROM W1 LEFT JOIN W2 ON W1.Key1W1 = W2.Key1W2 AND W1.Key2W1 = W1.Key2W2

Simple Full Outer Join: One-One

The key columns all have a required FIRSTNONNULL expression in it.

CREATE OUTPUT WINDOW OW2
PRIMARY KEY (Key1, Key2) 
SELECT FIRSTNONNULL(W1.Key1W1, W2.Key1W2) Key1, FIRSTNONNULL(W1.Key2W1, W2.Key2W2) Key2, W1.*, W2.* 
FROM W1 FULL JOIN W2 ON W1.Key1W1 = W2.Key1W2 AND W1.Key2W1 = W1.Key2W2

Simple Left Join: One-Many

All the keys of W2 are mapped and only one key of W1 is mapped in this join. The many-side is W1 and the one-side is W2. The keys must be derived from the many-side.

CREATE OUTPUT WINDOW OW3
PRIMARY KEY (Key1W1, Key2W1) 
SELECT W1.*, W2.*
FROM W1 LEFT JOIN W2 ON W1.Key1W1 = W2.Key1W2 AND W1.Val2W1 = W1.Key2W2

Simple Inner Join: Many-Many

This is a many-many join because neither of the keys are fully mapped. The keys of the target must be the keys of all the windows participating in the join.

CREATE OUTPUT WINDOW OW3
PRIMARY KEY (Key1W1, Key2W1, Key2W1, Key2W2) 
SELECT W1.*, W2.*
FROM W1 LEFT JOIN W2 ON W1.Val1W1 = W2.Val11W2 AND W1.Val2W1 = W1.Val22W2

Simple Stream-Window Left Join

When a left join involves a stream, the stream must be on the outer side. The target cannot be a window unless it is also performing aggregation.

CREATE OUTPUT STREAM OSW1
SELECT S1.*, W2.*
FROM S1 LEFT JOIN W2 ON S1.Key1S1 = W2.Key1W2 AND W1.Val2W1 = W1.Key2W2

Complex Join

The keys for OW4 can be derived either from W1 or W2 because of the inner join between the two tables.

CREATE OUTPUT WINDOW OW4
PRIMARY KEY DEDUCED
SELECT S1.*, W1.*, W2.*, W3.*
FROM W1 INNER JOIN (W2 LEFT  JOIN W3 ON W2.Key1W2 = W3.Key1W3) ON W1.Key1W1 = W2.Key1W2 AND W1.Key2W1 = W2.Key2W2;

Complex Stream-Window Join

Here, the join is triggered only when a record arrives on S1. Also, because there is aggregation, the target must be a window instead of being restricted to a stream.

CREATE OUTPUT WINDOW OW5
PRIMARY KEY DEDUCED
SELECT S1.* W1.*, W2.*, W3.*   //Some column expression.
FROM S1 LEFT JOIN (W1 INNER JOIN (W2 LEFT  JOIN W3 ON W2.Key1W2 = W3.Key1W3) ON W1.Key1W1 = W2.Key1W2 AND W1.Key2W1 = W2.Key2W2) ON S1.Val1S1 = W1.Key1.Val1
WHERE W2.Key1W2 = 'abcd'
GROUP BY W1.Key1W1, W1.Key2W2
HAVING SUM(W3.Val1W3) > 10;