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 W3 SCHEMA (Key1W3 integer, Val1W3 integer, Val2W3 string) PRIMARY KEY (Key1W3);
Here, keys can be derived from either W1 or W2.
CREATE OUTPUT WINDOW OW1 PRIMARY KEY (Key1W2, Key2W2) AS SELECT W1.*, W2.* FROM W1 INNER JOIN W2 ON W1.Key1W1 = W2.Key1W2 AND W1.Key2W1 = W2.Key2W2;
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 OW2 PRIMARY KEY (Key1W1, Key2W1) AS SELECT W1.*, W2.* FROM W1 LEFT JOIN W2 ON W1.Key1W1 = W2.Key1W2 AND W1.Key2W1 = W2.Key2W2;
The key columns all have a required firstnonnull expression in it.
CREATE OUTPUT WINDOW OW3 PRIMARY KEY (Key1, Key2) AS 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 = W2.Key2W2;
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 OW4 PRIMARY KEY (Key1W1, Key2W1) AS SELECT W1.*, W2.* FROM W1 LEFT JOIN W2 ON W1.Key1W1 = W2.Key1W2 AND W1.Val2W1 = W2.Key2W2;
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 OW5 PRIMARY KEY (Key1W1, Key2W1, Key1W2, Key2W2) AS SELECT W1.*, W2.* FROM W1 JOIN W2 ON W1.Val1W1 = W2.Val1W2 AND W1.Val2W1 = W2.Val2W2;
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 AS SELECT S1.*, W2.* FROM S1 LEFT JOIN W2 ON S1.Val1S1 = W2.Key1W2 AND S1.Val3S1 = W2.Key2W2;
The keys for OW4 can be derived either from W1 or W2 because of the inner join between the two tables.
CREATE OUTPUT WINDOW OW6 PRIMARY KEY DEDUCED AS 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.Key1W1 WHERE W2.Key2W2 = 'abcd' GROUP BY W1.Key1W1, W2.Key2W2 HAVING SUM(W3.Val1W3) > 10;
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 OW7 PRIMARY KEY DEDUCED AS 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.Key1W1 WHERE W2.Key2W2 = 'abcd' GROUP BY W1.Key1W1, W2.Key2W2 HAVING SUM(W3.Val1W3) > 10;