Joy of JOINs

The next release of the Eloquera DB will include a few new features, the main being return of JOINs. The Eloquera DB implements all type of JOINs now:

• Inner JOIN
• LEFT and RIGHT OUTER JOINs
• FULL JOIN

Adding indexes to the fields that are joined will speed up the queries exponentially.

Of course, it is better to avoid join queries by designing your data in the object-oriented way like nesting objects, inheriting classes, etc. For example, it is better to search for all the products of some specific company by having a class like below and then inheriting from it:

Just a note: in the classes that are going to be stored and are not public, automatic properties double amount of data to be stored and retrieved without adding any benefit from the data store point of view.

The query in this case will be pretty simple:

No joins, nothing complex. And use parameters everywhere – no string concatenations, please.

The JOIN queries are perfect for matching heterogeneous data from different sources and having different structure. There may be myriads of scenarios where the join queries can be handy. And therefore they are back into the Eloquera DB.

Currently, JOIN queries are working for native objects only. That means that they work with everything except Dynamic objects. And the question is if it is really needed. The plan is to add JOINs for Dynamics, too, but not this time. Here is when we need to ask all developers if they see need in such functionality.

There will be a few differences how JOINs work for the native objects and for the Dynamic objects. First of all, Dynamic objects are not distinguished by type, and therefore Dynamic objects can only JOIN other Dynamic objects. It means that most of the time developers will be writing the self-join queries against Dynamic objects. The question is how often that may be required if at all.

Second potential issue is that Dynamic objects return null when requested a field not defined in the object. This renders all OUTER JOINs almost useless – they will either return all Dynamic objects paired with all other Dynamic objects (like FULL JOIN) or return only Dynamic objects with a specific field being not null (and this is the behaviour of INNER JOIN).

So, having all this said – can anyone devise any valid scenarios for JOIN for Dynamic objects? We would like to have an open discussion about this – every opinion counts.