INNER (set inner join operation)

Top  Previous  Next

 

INNER

The INNER attribute (PROP:INNER) specifies the JOIN structure declares an "inner join" instead of the default "left outer join."

The VIEW structure defaults to a "left outer join," where all records for the VIEW's primary file are retrieved whether the secondary file named in the JOIN structure contains any related records or not. Specifying the INNER attribute on the JOIN creates an "inner join" so that only those primary file records with related secondary file records are retrieved. Inner joins are normally more efficient than outer joins.

PROP:INNER is an array property of a VIEW indicating the presence or absence of the INNER attribute on a specific JOIN. Each array element returns one ('1') if the JOIN has the INNER attribute and blank ('') if it does not. The JOINs are numbered within the VIEW starting with 1 as they appear within the VIEW structure.

PROP:INNER is read-only for non-SQL views and read/write for SQL based views.

Example:

AView  VIEW(BaseFile)

       JOIN(ParentFile,'BaseFile.parentID = ParentFile.ID')      !JOIN 1

        JOIN(GrandParent.PrimaryKey, ParentFile.GrandParentID)   !JOIN 2

        END

       END

       JOIN(OtherParent.PrimaryKey,BaseFile.OtherParentID),INNER !JOIN 3

       END

      END

 

! AView{PROP:Inner,1} returns ''

! AView{PROP:Inner,2} returns ''

! AView{PROP:Inner,3} returns '1'

 

ViewOrder VIEW(Customer),ORDER('-Hea:OrderDate,Cus:Name')

         PROJECT(Cus:AcctNumber,Cus:Name,Cus:Zip)

         JOIN(Hea:AcctKey,Cus:AcctNumber),INNER    !Inner Join on Header

          PROJECT(Hea:OrderNumber,Hea:OrderDate)   ! gets only custmers with orders

          JOIN(Dtl:OrderKey,Hea:OrderNumber),INNER !Inner join on Detail file

           PROJECT(Det:Item,Det:Quantity)          ! is natural and more efficient

           JOIN(Pro:ItemKey,Dtl:Item),INNER        !Inner join on Product file

            PROJECT(Pro:Description,Pro:Price)     ! is natural and more efficient

           END

          END

         END

         END

 

See Also:

JOIN