# Add support for Left and Outer Joins - F32534

Idea created by Langevin on Sep 5, 2014
Under review
Score25

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)

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)

Where DESIRED x joins SOME y

Where DESIRED y joined DESIRED x

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)

Where DESIRED y omits SOME 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:

Where SOME u.widget_id  = SOME v.widget_id

Where desired u.widget_id = desired v.widget_id

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.