How Does the Automatic Join Feature Work?

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

If primary key or foreign key information is registered in the metadata for the table that you drag and drop onto the workspace, then the data builder retrieves the foreign key and primary key information.

The data builder then iterates over each of the tables that are already in the workspace in the same sequence in which they were added to the workspace. The data builder retrieves the foreign key 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 data builder continues to search for matches between the two tables, and it adds join conditions when possible. After a set of join tables is identified, the data builder does not continue iterating over the tables that are already in the workspace.

If there is no foreign key or primary key information for the table that is dragged and dropped onto the workspace, then the data builder does not use foreign key or primary key information for the tables already in the workspace.

The data builder retrieves the column information for all of the columns in the newly added table. The data builder then iterates over each of the tables that are already in the workspace in the same sequence in which they were added to the workspace. The data builder compares the length, type, and name for each column 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 data builder does not continue to search for matches between the two tables, and it does not continue iterating over the tables that are already in the workspace.

When the data builder finds the first set of matching columns by comparing keys or matching names, it sets the join type for the two tables. The data builder checks the metadata for the columns to determine whether the columns are nullable. (Many third-party vendor databases support NOT NULL as a constraint for a column when the table is created in the database with SQL. The constraint ensures that there are no missing values for the column.) The data builder 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.