Add support for optional edges to the SQL backend.

[jmeulemans]

The rule of thumb for mapping an edge to a JOIN statement is that if the edge is required, an INNER JOIN should be used, and if the edge is optional a LEFT JOIN should be used. This applies to all tables involved in both direct and many-to-many JOINs, with one notable exception.

When an edge is required within an optional scope, the compiler semantics state that if the
outer optional edge is present, but the inner required edge is not, this result should be
excluded. For example with the GraphQL query:

{
    Animal {
        name @output(out_name: "name")
        out_Animal_ParentOf @optional {
            name @output(out_name: "child_name")
            out_Animal_ParentOf {
                name @output(out_name: "grandchild_name")
            }
        }
    }
}

An animal that has a child (satisfying the first optional ParentOf edge), but where that child has no children (failing to satisfy the second required ParentOf edge) should produce no result. Using nested INNER JOINs here from the outer LEFT JOIN, like

SELECT
    animal.name as name,
    child.name as child_name,
    grandchild.name as grandchild_name
FROM animal
LEFT JOIN (
    animal AS child
    INNER JOIN (
        animal as grandchild
    ) ON child.parentof_id = grandchild.animal_id
) ON animal AS child ON animal.parentof_id = child.animal_id

will have a NULL value returned for the grandchild.name property. The LEFT JOIN condition is fulfilled but the INNER JOIN condition is not, which doesn't exclude the result but rather includes it with a NULL value.

To get the correct semantics, the result when the INNER JOIN condition is not fulfilled needs to be filtered out. This is done explicitly by replacing the INNER JOIN with a LEFT JOIN, and then applying the JOIN condition in the WHERE clause to the rows that are non-null from the LEFT JOIN. For this example this looks like:

SELECT
    animal.name as name,
    child.name as child_name,
    grandchild.name as grandchild_name
FROM animal
LEFT JOIN (
    animal AS child
    INNER JOIN (
        animal as grandchild
    ) ON child.parentof_id = grandchild.animal_id
) ON animal AS child ON animal.parentof_id = child.animal_id
WHERE
    child.animal_id IS NULL
    OR
    child.parentof_id = grandchild.animal_id -- reapply JOIN condition in WHERE clause

The null check ensures that the filter is only applied iff the LEFT JOIN condition is actually
satisfied.

[obi1kenobi]

Closed by #528.