Multi-table association refers to connecting multiple tables through data associations to form a model for data analysis when the data to be analyzed is stored across different tables. Multi-table association typically supports the following association modes:
Left join (left outer join): The returned result includes all records from the left table and matching records from the right table where the associated fields are equal.
Right join (right outer join): The returned result includes all records from the right table and matching records from the left table where the associated fields are equal.
Inner join: Only the rows where the associated fields in the two tables are equal are returned.
Full join (full outer join): The returned result is the union of the left join and right join results.
Example
Table A
Table B
1. left join
The SQL statements are as follows:
select * from A
left join B
on A.aID = B.bID
The results are as follows:
Result description
A left join uses Table A as the base. All records from the left table (A) will be displayed, while only the records that meet the search criteria in the right table (B) will be displayed (A.aID=B.bID in this example). Unmatched records from Table B are displayed as NULL.
2. right join
The SQL statements are as follows:
select * from A
right join B
on A.aID = B.bID
The results are as follows:
Result description:
The result of a right join is the opposite. A right join uses the right table (B) as the base. Unmatched records from the left table (A) are filled with NULL.
3. inner join
The SQL statements are as follows:
select * from A
inner join B
on A.aID = B.bID
The results are as follows:
Result description:
Here, only records where A.aID=B.bID are displayed. This indicates that an inner join is not based on either table but only displays the records that meet specified conditions.
4. full join
The SQL statements are as follows:
select * from A
full join B
on A.aID = B.bID
The results are as follows:
Result description:
Here, the result is the union of Table (A) and Table (B). Unmatched records from both tables are filled with NULL.
Creating Multi-table Association
Select Data, Data Table, and then Create Data Table. In the pop-up window for selecting a table creation method, select the "Join Tables" entry.
Go to the multi-table association page. The default state is as shown below. The left side is the data table selection area, the top right is the canvas area, and the lower right is the data preview and field configuration area.
Step one: select the data table to be used for association. First, choose the source of the data table, divided into data source, Excel upload, and Tencent Documents.
After a specific data table source is selected, the "Select data table" section below will display the data tables from the selected source. For example, if you select "Excel upload", all data tables from Excel uploads will be displayed, and you can search for a specific data table.
FirstFirst, drag and drop the primary table to the canvas area. At this point, the data preview section below will preview the first 50 data entries of the table.
Then drag and drop the attached table next to the student list table on the canvas. At this moment, a dashed box will appear, representing the placement location. After placing it, you need to configure the relationship between the two tables. First, select the association method, which is divided into left outer join, right outer join, and inner join. For the meaning of each join, see What Is Multi-table Association.
Then, select associated fields. By default, fields with the same name in both tables will be used as the associated fields. This field will be selected by default for association. You can also add or delete the associated fields based on business needs.
Finally, perform field selection. Field selection refers to choosing which fields to display in the associated data table. By default, all fields are selected. If not required, you can uncheck certain fields to hide them.
Click Save to preview the associated data table. At this point, you can see that the student roster and math score table are joined together.
On this basis, multi-table join also supports associating more than two data tables.
If you need to edit fields in the associated table, such as deleting specific fields, modifying field display names, or changing field types, you can perform the operations in field configurations.
After the association, click Save at the top right of the page, then enter the display name and folder (optional) of the new data table to complete its creation.
