Joins

In order to perform cross-table operations, it is first necessary to join the tables.   

The Joins dialog is opened via Engineering | Joins.

Joins can be also created by dropping the primary key field onto the foreign key field in the Database explorer.

 Primary and foreign

The primary table is the ONE side of a ONE-to-MANY relationship.

The foreign table is the MANY side of a ONE-to-MANY relationship.

Join restrictions

Joins are only possible between continuous fields that have a matching field-type (for example STRING to STRING, DATE to DATE).  This restriction does not apply to discrete fields.

If the primary field is continuous, then the foreign field must also be continuous. TODO: Confirm

Only one default join can exist at a time between tables.

The direction in which tables are joined will affect the results returned - pay careful attention to this when engineering and ensure that the results returned are as intended.

DataSet Operations across un-joined tables will return a {...} result.

Join performance

Creating joins usually takes seconds, but on tables of hundreds of millions of rows it could take some minutes to link continuous fields.  It is significantly faster on very large tables to work with numeric keys rather than string keys.

Working with joins in script

Joins can be added to scripts by using the AddJoin and DeleteJoin method from Script Editor | methods | data model

Use Script Editor | Import Definitions to add existing joins to scripts