Data mining — Table joins

On the canvas of the Design Studio, the selected tables must be joined to relate the data. Joining tables results in the definition of a single logical input table.

Tables are joined by using shared columns and particular join types. For example, in the following table, the tables Product and Sales transactions can be joined by using the shared column PD_ID.

Table 1. Table selection
Table Name	Logical Name	Data	Column Names	Descriptive Column Names
PRCHS_PRFL_ANLYSIS	Sales transactions	Point-of-sales	TIME_ID STR_IP_ID PD_ID SALES_AMOUNT CST_OF_GDS_SLD_CGS	TIME_ID STR_IP_ID PD_ID SALES_AMOUNT Cost
STORE	Store	Store properties	STR_IP_ID STR_REG_NM STR_TP_NM	STR_IP_ID Store region Store type
PRODUCT	Product	Product properties	PD_ID NM PD_DEPT_NM	PD_ID Name Department name
TIME	Time	Time properties	TIME_ID CDR_YR CDR_QTR CDR_MO DAY_CDR_MO	TIME_ID Year Quarter Month Day

Join types

To join tables, you can use the following join types:

Inner

You can use the column STR_IP_ID to join the tables Sales transactions and Store with the Inner join type.

The Inner join type assumes that all stores are included in the table Store so that rows from the table Sales transactions are not left out of the analysis.

Inner joins can be thought of as the cross product of the tables. Each row of the left table is combined with every row of the right table. Only the rows where the join condition is true are kept. In the result table, rows from each of the joined tables or from both might be missing.

Outer

Outer joins include the inner joins. Additionally, they preserve the missing rows of the inner joins.

The following types of outer joins are available:

Left Outer

You can use the column PD_ID to join the tables Sales transactions and Product with the Left Outer join type.

The Left Outer join type ensures that sales data for new products that might still be missing in the table Product is not left out of the analysis.

The left outer join includes rows from the left table that are missing from the inner join.

Right Outer

The Right Outer join type is not used in the Store Sales Forecasting sample.

The right outer join includes rows from the right table that are missing from the inner join.

Full Outer

You can use the column TIME_ID to join the tables Sales transactions and Time with the Full Outer join type.

The full outer join ensures that sales data and time data that might still be missing in the tables Sales transactions and Time is not left out of the analysis.

The full outer join includes rows from the left table and the right table that are missing from the inner join.

Because of rolling window definitions, all day/month/year values are mandatory in the tables.