About Joins

SAS Visual Data Builder supports joins for tables and queries. You can join tables to each other, including self joins. You can also join queries to tables and queries to queries. When you use a query in a join, the join condition is made against the output table for the query.

When you drag a table or query onto the workspace, the application attempts to determine a join condition automatically. The application retrieves the table metadata that is registered with the SAS Metadata Server. If a foreign key reference is registered with the table metadata, then the application adds a join based on the foreign key information. If there is no foreign key information stored in metadata, then the application compares column names and types to determine whether there is a match. If a match is found, then the matching columns are used to perform the join.

When the application creates a join automatically, using foreign keys or by matching columns, the join is added as an inner join. The application also supports left, right, and full joins. You need to specify the join type manually if you do not want an inner join.

In addition to the ease with which joins are added to a query, SAS Visual Data Builder also generates an SQL statement with all the joins declared explicitly. For example, you can specify an inner join in a WHERE clause, such as WHERE t1.order_id=t2.order_id. However, mixing inner joins in a WHERE clause and outer join types in a single query can be complex to read and understand.

LEFT JOIN
        LIB1.TRANSACTION_TYPE_DIM TRANSACTION_TYPE_DIM 
            ON CASH_FLOW_FACT.TRANSACTION_TYPE_KEY = 
                TRANSACTION_TYPE_DIM.TRANSACTION_TYPE_KEY 
    INNER JOIN
        LIB1.TRANSACTION_DIM TRANSACTION_DIM 
            ON PARTY_DIM.SEGMENT_ID = TRANSACTION_DIM.SEGMENT_ID 
            AND PARTY_DIM.TRANSACTION_KEY = TRANSACTION_DIM.TRANSACTION_KEY