How Does the Automatic Join Feature Work?

SAS Visual Data Builder attempts to join tables and queries automatically as you add them to the workspace. When you drop a table or query onto the workspace, information about the table is retrieved from the SAS Metadata Server. For queries, the metadata for the output table is retrieved.

If primary or foreign key information is registered in metadata for the table that you drop on the workspace, then the application retrieves the foreign and primary key information.

The application then iterates over each of the tables that are already on the workspace in the same sequence that they were added to the workspace. The application retrieves the foreign and primary key information for the table and compares the length, type, and name with the key columns for the newly added table. If a match is found, then the tables are used in the join and the columns are added as a join condition. The application continues to search for matches between the two tables and adding join conditions. Once a set of join tables is identified, the application does not continue iterating over the tables that are already on the workspace.

If there is no foreign or primary key information for the table that is dropped on the workspace, then foreign or primary key information for the tables already on the workspace is not used.

The application retrieves the column information for all the columns in the newly added table. The application then iterates over each of the tables that are already on the workspace in the same sequence that they were added to the workspace. The length, type, and name for each column is compared with each column in the newly added table. If a match is found, then the tables are used in the join and the columns are added as a join condition. The application does not continue to search for matches between the two tables and does not continue iterating over the tables that are already on the workspace.

When the application finds the first set of matching columns, by comparing keys or matching names, the application sets the join type for the two tables. The application checks the metadata for the columns to determine whether the columns are nullable. The application performs the following steps to set the join type:

You can specify the join type by right-clicking on the join icon and selecting the join type from the menu.