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