Add support for Left and Outer Joins - F32534

Idea created by Langevin on Sep 5, 2014
    Under review

    All relations in Gen fall into one of the following categories:

    • Fully Mandatory,
    • Fully Optional,
    • Partially Mandatory,
    • Denormalized (no direct relationship)


    I believe that modelling these properties allows us to determine when we should like to have Left Joins, and Outer Joins, be available.


    Fully Mandatory relations imply strictly Inner Joins.  For example:

    S is header for T, t is details of S (the relation is fully mandatory)

    Read each S,T

    Where DESIRED S is header for DESIRED T


    The example above is a classic inner join.  This is functionality we have today.


    Partially Mandatory relations imply that Parents may exist without children.  I propose that under these conditions the relation view should allow us to specify a left join.  For example :

    x joins y; y joined  x (the relation is partial mandatory, x is required for  y, but x can stand alone)

    (1) Read Each x, y

    Where DESIRED x joins SOME y

    (2) Read Each x,y

    Where DESIRED y joined DESIRED x

    (3) Read each x,y

    Where SOME y joined Desired x


    Cases 1 and 2 above are different in that the where clause is specifying which desired data is required. When the child is desired, the Left Join is not available since every child must have a parent (given our relationship).  1 is a left join, but 2 is a classic inner join.  I believe case 1 today is treated as an inner join but see no reason for this to be necessary.  Case 3 is a left join using the relation read from the child's perspective.  Code wise it would be functionally equivalent to 1.


    Fully Optional relations imply that either parent or child can exist without the other.  For example:

    Y Omits Z; Z omitted  Y (fully optional)

    (1) Read each y,z

    Where DESIRED y omits SOME z

    (2) Read each y,z

    Where SOME y omits  SOME z


    Case 1 above shows an outer join in which Y is desired for any Zs that may exists.

    Case 2 above shows a full outer join.


    Denormalized relations

    Widget id is found in 2 tables (u,v) with no obvious relation.

    Instead of using relation views to join the tables in  a read each, attributes are used to join the tables.  For example:

    (1) Read each U,V

    Where SOME u.widget_id  = SOME v.widget_id

    (2) Read each U,V

    Where desired u.widget_id = desired v.widget_id

    (3) Read each U,V

    Where Desired u.widget_id = SOME v.widget_id


    1 would be the full outer join, 2 would be the classic inner join, 3 is the left join


    I haven't considered how using the keyword SOME will impact existing functionality, but used it for illustrating this point.  ANY as a keyword would work just as well.