About Joins

SAS Visual Data Builder supports joins for tables and subqueries. You can join tables to each other, including self joins. You can join subqueries to tables and join subqueries to subqueries. When you use a subquery in a join, the join condition is made against the output table for the subquery. The data builder supports joining up to 256 tables.

When you drop a table or subquery onto the workspace, the data builder attempts to determine a join condition automatically. When the data builder creates a join automatically using foreign keys or by matching columns, the join is added as an inner join. The data builder also supports left, right, and full joins. You must specify the join type manually if you do not want an inner join.

In addition to easily adding joins to a data query, the data builder generates an SQL statement with all of 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 data query can be complex to read and understand.

SAS Visual Data Builder always generates an SQL statement with inner joins declared explicitly. For example, see the following code sample:

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