##! experimental.join_types

[lloydtabb]

experimental.join_types

December 4, 2023 - lloydtabb

Currently Malloy only supports a left join. This experiment addes all the other join types (inner, full, right) as optional. To turn on this experiment the following code to the top of your file.

##! experimental.join_types

The current syntax to use this experiment

  join_[one|many|cross]: name [is <source>] [left|inner|full|right] on <condition>

Considerations

Its not clear that this is correct. For the most part, you would like to use these joins in a query: block instead of a source: block but we only allow adding joins to sources.

In Malloy, joins are only included when a measure or dimension from that join is included. Inner joins operate as filters. When you include an inner join, you usually want that join to execute regardless if something is included or not.

Including an inner join in a source might be confusing as it would filter the source if something is referenced.

Perhaps these joins should only be allowed in queries and perhaps they should always execute.

Having join_types available will allow us to figure out what is right. I'm pretty sure this experimental implementation has problems as implemented.

[lloydtabb]

More on the underlying problem.

We initially implemented only left joins because in a source: when you are describing the network of data, you want to describe all the possible dimensions and measures. If you have an 'inner' join in a source, reduces the network to only data on the right hand side of the join.

For example, if you had flights and you inner joined california airports, flights would be reduced to only California flights. In a query, you can expect and like this behavior, but in a source, simply grouping by california_airports.code would eliminate all the flights outside of californa.

For this reason, I'm not sure we should allow inner joins in sources. right joins also have this problem.

These joins in queries are important and powerful.

We need a syntax that reflects this.