All the queries that were discussed in the previous topics were based on a single table. However, one cannot restrict the queries to a single table and so applying queries on multiple tables becomes important.

Join is a very important feature of DataEngine queries that allows you to combine (blend) data from several tables (base tables or queries). Join query is a special kind of query that does not have any calculation or aggregation operations. The only purpose it serves is that of joining tables. If you need calculations and/or aggregation subtotals, you can do this on joined tables as well since all the DataEngine queries, including joins, can be combined as without restrictions as discussed in Combining Operations and Queries topic.

Joins in DataEngine are always many-to-one, which corresponds to the common "star schema" convention in data analytics. So there is one main table and one or more 'attached' tables that are linked to the main table. Join result contains the same number of rows as the main table. Every joined row contains a value for each main table column included in the result (not necessarily all columns are included, although including all columns is a common case) and a value for each included column of each linked table (usually only some of the columns of linked tables are included).

The following example illustrates a Join.

Syntax

Dim jq As dynamic = workspace.join("query_join", od, New With { _
        Key .od = od.ProductID + od.OrderID, _
        Key .pr = pr.UnitsInStock Or od.ProductID = pr.ProductID _
})

 dynamic jq = workspace.join("query_join", od, new
    {
        od = od.ProductID + od.OrderID, // get ProductID and OrderID from the OrderDetails table
        pr = pr.UnitsInStock | od.ProductID == pr.ProductID // get UnitsInStock from the Products table and join OrderDetails and Products table on the ProductID field
    });

Main table is specified in the second parameter, od (it is our old OrderDetails table). First line applies to the main table and defines which columns we take from the main table to the result. In this case we take two columns, ProductID and OrderID. The main table line is optional. If it's not present, all columns from the main table are included in the join.

Second line defines a linked table, Products, and fields we take from it, UnitsInStock. It also defines, after the separator | how we link Products to the main table OrderDetails. A linking (join) condition must be an equality between one or more columns from each side (they are usually called key columns).

Here is a more general example showing that there can be more linked tables than one (but only one main table) and join conditions can have more than one key column (Main table - Orders; Linked tables - Customers, Employees).

Syntax

Dim jq As dynamic = workspace.join("join2", orders, New With { _
        Key .customers = customers.Company + customers.Country Or orders.CustomerID = customers.customerID, _
        Key .employees = employees.Name + employees.Title Or orders.EmployeeID = employees.EmployeeID + orders.CustomerID = customers.customerID _
})

dynamic jq = workspace.join("join2", orders, new
    {
        customers = customers.Company + customers.Country | orders.CustomerID == customers.customerID,
        employees = employees.Name + employees.Title | orders.EmployeeID == employees.EmployeeID + orders.CustomerID == customers.customerID
    });

By default, columns in the result keep their names that they had in the main and in the linked tables. However, sometimes these names can collide, so you need to change the name of a column in the result (or might just want to give a column a different name).

Main table columns always keep their names, but names of linked columns can be changed with As("alias") as illustrated below:

customers = customers.Company.As("CustomerCompany") + customers.Country | orders.CustomerID ==
    customers.customerID,