Links

Pre-Built Links

Whereas a "standard" relational database system requires the join details of a query to be specified explicitly whenever a query is run, Engine makes use of pre-built links. The advantage of this is a huge reduction in query resolution time - the disadvantage is non-standard query syntax and less flexibility in the execution of queries.

Permanent Links

Permanent links - often referred to as Direct links - are set up prior to calculation, typically at the end of a load process. A Permanent link is created by specifying the key (or matching) columns from the tables to be linked. One of the columns must be unique in order for the link to be created.

Link Files

Links are stored in files in the repository. The larger the link file, the longer it will take to execute a query that crosses a link. When creating links it is important that there is sufficient disk space to store the link.

INNER JOIN

Links within Engine are automatically created as an INNER JOIN. The records returned from the query are always from a specific table - called the resolution table.

The resolution table of the query will default to the MANY side of a [ONE to MANY] (Parent/Child) link although other tables can be selected via use of the DistinctRow keyword.

Consider the following data structure;

The following query is executed;

SELECT* FROM Customer, Transactions; 		

No join information is included in the query. Because a link already exists between the two tables, Engine knows how the two tables are joined and will automatically return the linked records.

The records returned automatically relate to the transactions table as this is the MANY (child) side of the link. Only records that are linked between the tables are returned;

Record T6 and T9 from the transaction table are not returned because they are not linked to the Customer table. Likewise, record C5 is not returned from the Customer table as it is not linked to the transaction table.

OUTER JOIN

Referential integrity is not enforced when a link is created - it is possible to have orphaned records in both tables. These records are automatically excluded from an NSQL query. In standard SQL, to return these records, an Outer Join query would be executed. However, Engine has only limited support for Outer Joins.

Because the results of a query are always owned by a table, it is not possible to return records that do not belong to that table. In this sense, it is not possible to create a FULL outer join.

Left and Right Outer Joins cannot be created using a single NSQL statement, but can be created using Engine's Domain Manipulation capability if necessary.

SELF JOIN

Engine does not provide direct support for Self Joins. To execute a Self Join, a temporary table must be first be created.

MANY to MANY JOIN

Engine does not support MANY to MANY Joins. If this type of link is required, an intermediate table must be created;