Help Center/ DataArts Insight/ User Guide/ Creating a Data Model/ Creating a Model by Joining Multiple Tables
Updated on 2025-05-20 GMT+08:00
View PDF
Share

Creating a Model by Joining Multiple Tables

During the process of data analysis, you may come across the need to work with data scattered across different tables. By performing multi-table joins, you can connect these scattered tables and build a comprehensive data model for in-depth analysis. DataArts Insight supports LEFT JOIN, INNER JOIN, RIGHT JOIN, and FULL JOIN. This section walks you through how to perform multi-table join modeling.

Prerequisites

  • You have subscribed to DataArts Insight.
  • A project has been created by referring to Creating a Project.

Procedure

  1. Log in to the DataArts Insight console.
  2. Click in the upper left corner of the management console to select a region. Then, select an enterprise project in the upper right corner.
  3. On the top menu of the console, click Project. On the displayed My Projects page, click the name of the desired project.
  4. Choose Data Management > Datasets. On the displayed page, click Create Dataset.
  5. Select the required data source, database name, and schema.
  6. Select or create associated tables, supporting options such as selecting a data table to associate with another data table, creating a table by associating it with SQL code, or creating a table by associating it with data from SQL code.

    For how to edit SQL code, see Creating a Dataset Using SQL Statements. Only one SQL data table can be created in a dataset.

    1. To associate data tables, drag and drop the tables you want to link from the left side onto the operation panel. See Figure 1.
      Figure 1 Associating a data table with another data table
    2. To associate a data table with a table generated through SQL code, do as follows:
      1. Drag and drop the table from the left side to the operation panel, and click Edit SQL Statement to access the SQL editing page.
      2. Once the SQL statement is edited, click Run. After the execution is successful, click Finish and Exit.
      3. On the dataset management page is displayed, check that the tables are associated.
        Figure 2 Creating a table using sql code
        Figure 3 Associating a data table with a table generated through SQL code
    3. To associate a table generated through SQL code with a data table, do as follows:
      1. Click Edit SQL Statement to access the SQL editing page.
      2. Once the SQL statement is edited, click Run. After the execution is successful, click Finish and Exit.
      3. On the dataset management page, drag and drop a data table from the left to associate them.
        Figure 4 Creating a SQL table
  7. Edit the table association relationship.
    1. If the data model to be analyzed is complex and multiple tables need to be associated, use an association relationship to select the endpoint table.
      Figure 5 Selecting an endpoint table
    2. Select table fields as needed.
    3. Select an association relationship.
      • LEFT OUTER JOIN: Returns all records from the left table, regardless of whether there is a matching record in the right table. For any records in the right table that do not have a match, the corresponding fields in the result will be displayed as NULL.
      • INNER JOIN: Returns the records that have matching values in both tables, essentially showing the common elements between the two tables.
      • RIGHT OUTER JOIN: Returns all records from the right table, regardless of whether there is a matching record in the left table. For any records in the left table that do not have a match, the corresponding fields in the result will be displayed as NULL.
      • FULL JOIN: Returns all records from both tables, regardless of whether they have a match. Any duplicate entries will be eliminated.
    4. Configuring associated fields: When configuring data associations, the associated fields must be selected from the existing fields in the table being associated.
  8. Click OK to complete data association. Click Refresh Preview to view the associated data.
Parent topic: Creating a Data Model