Setting the Implicit Property for a Join

Problem

You want to decide whether the Implicit property for a join should be enabled. This setting determines whether the join condition is processed implicitly in a WHERE statement or explicitly in a FROM clause in the SELECT statement.

Solution

You can access the Implicit property in the SQL Properties pane. You can also right-click a join in the Diagram tab to access the property in the pop-up menu. The following table depicts the settings that are available for each type of join, along with a sample of the join condition code that is generated for the join type:
Implicit and Explicit Properties for SQL Join Types
Join Type
Join Condition Code
Inner
Can generate an implicit inner join condition in a WHERE statement near the end of the query:
where
   POSTALCODES.Name = UNITEDSTATES.Name
You can use an implicit join only when the tables are joined with the equality operator. You can also generate an explicit inner join condition in a FROM clause in the SELECT statement:
from  
    srclib.POSTALCODES inner join 
            srclib.UNITEDSTATES
               on
               (
                  POSTALCODES.Name = UNITEDSTATES.Name
               )
Full
Can generate an explicit join condition in a FROM clause in the SELECT statement:
from
   srclib.POSTALCODES full join 
            srclib.UNITEDSTATES
               on
               (
                  POSTALCODES.Name = UNITEDSTATES.Name
               )
Left
Can generate an explicit join condition in a FROM clause in the SELECT statement:
from
   srclib.POSTALCODES left join 
            srclib.UNITEDSTATES
               on
               (
                  POSTALCODES.Name = UNITEDSTATES.Name
               )
Right
Can generate an explicit join condition in a FROM clause in the SELECT statement:
from
   srclib.POSTALCODES right join 
            srclib.UNITEDSTATES
               on
               (
                  POSTALCODES.Name = UNITEDSTATES.Name
               )
Cross
Can generate an explicit join condition in a FROM clause in the SELECT statement:
from
   srclib.POSTALCODES cross join 
   srclib.UNITEDSTATES
Union
Can generate an explicit join condition in a FROM clause in the SELECT statement:
from
   srclib.POSTALCODES union join 
   srclib.UNITEDSTATES
The Implicit property is disabled by default for all of the join types except the inner join.
Copyright © SAS Institute Inc. All rights reserved.