Joins

Version: 3.4.1+

Starting with version 3.4.1, you have the possibility join tabular data with other tables or views. After enhancing your data by performing one or multiple joins, all the other operations that are available on tabular data (e.g. custom fields, creating graphs, aggregating data through Measures) are still available.

Joining with other tables/views

There are two ways to perform a join: opening the Joins window or by one-click blitz joining. We will go through them one by one.

Joins window

To create a join through the joins window:

• open the joins window by clicking the Join data button of the widget containing the tabular data.
• click on the Add join button
• an entry for creating a new join will be created. A typical join entry consists of:
  • the Join type. This is the SQL join type (e.g. left outer, inner, right outer) that will be performed to retrieve the data. The available join types depends on the database vendor (and the join types it supports) and Scai supported join types.
  • the table/view you would wish to join with
  • the Join alias. This is an alias that is given to the join and can be chosen at will. The only restriction is that aliases of multiple joins must be unique.
  • the Join conditions. These are a list of conditions to perform the join on. They consist of multiple pairs of columns where the left column can be chosen from the existing columns of the tabular data (including custom fields) and the right column is chosen from the list of columns of the table/view joined with.

    Scai will try to determine automatically which table/view you would like to join with, the type of the join, the alias and the join conditions. See Join recommendations.

• once the fields for the join entry are filled in, click on Save
• the join will be performed and the columns of the joined with table will be presented in the tabular data widget. If needed, you can restrict the set of columns or rename them using the Column settings.

(One-click) Blitz joining

To blitz join:

• identify the column you would like to use in the join (e.g. expand foreign key columns)
• move the mouse over any of its values
• click the Blitz join button
• Scai will then try to perform the most appropriate join for that column from the list of join recommendations. See Join recommendations for more information on how Scai determines which joins are appropriate for a given column.

Join recommendations

Given some tabular data (e.g. a table you're exploring) or a given column, Scai will try to determine the most appropriate joins that you might want to perform. In doing this, Scai aims to reduce the effort made by you (the user) by specifying all the information (join type, alias, conditions, etc.) to perform a join. These recommendations are made based on:

• the information available, i.e. the column(s) given, the foreign key definitions and the list of joins already performed
• a given priority to each join. This is computed, as follows:
  • joins based on foreign key relationships have the highest priority. These joins are the most common and you will likely be performing these in most cases.
  • in absence of foreign key relationships, use naming conventions to determine the most appropriate join. Example: authorId -> authors.id.

Supported join types

As long as the database vendor has native SQL support, Scai supports the following join types:

• (inner) join: this type of join will return the rows that have matching values in both tables. For data with no duplicates on the join keys (join condition columns),this corresponds with intersecting two data sets (e.g. the intersect part of a Venn diagram).
• left outer join: this type of join will return all the rows of the existing tabular data and the matching rows from the joined with table.
• right outer join: this type of join will return all the rows of the joined with table and the matching rows of the existing tabular data.
• full outer join: this type of join will return all the rows of the existing tabular data and all the rows of the joined with table, matching each of them on the join conditions.

Limitations

• Custom fields can be used in join conditions. However, multiple iterations of joining and adding custom fields are not supported.Example: you cannot join table A with table B, add a custom field c as concat(A.name, B.name), join with table D on c = D.name and then try to add a new custom field e based on columns from D
• Column visibility does not apply to tables that are joined.