Key field rules ensure that rows are not rejected due to duplicate inserts or the key fields being NULL.
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. |