JOINTYPE

Top  Previous  Next

 

DRIVER('SQLDriver', '/JOINTYPE = Watcom | DB2 | Microsoft | FirstSQL | Inner | None' )

[ Join" = ] SEND(file, '/JOINTYPE [ = Watcom | DB2 | Microsoft | FirstSQL | Inner | None ]' )

(NOTE: Not valid for ORACLE Accelerator)

The SQL standard does not support joins to more than one child (or parent). Most vendors consider this limitation unacceptable and have extended the standard. However, they have done so in different ways. The SQL driver attempts to determine the join type used by the backend, but if it does not get it right, then you should use the JOINTYPE driver string in the primary file of the view. Note that specifying Inner is normally slower than Watcom, DB2, Microsoft or FirstSQL. Specifying None is slower than Inner, but will work with all backends because the join is done on the client.

When using ODBC, the ODBC 3.0 standard does support multiple joins, so ODBC 3.0 compliant drivers should not require this switch.

JOINTYPE=DB2

This is the join syntax used by IBM's DB2. This generates ANSI compliant outer joins. The Base Normal Form for the relevant DB2 specification is:

from-clause ::=

 FROM <table-ref>

 

table-ref::=

   <single-table> |

   <joined-table>

 

single-table ::=

table-name AS correlation-name

 

joined-table ::=

  <table-ref> LEFT OUTER JOIN <single-table> ON join-condition

 

JOINTYPE=MICROSOFT

This is the join syntax specified by the ODBC 2.0 spec. The Base Normal Form for the relevant ODBC spec is:

from-clause ::=

   FROM <table-ref> |

   FROM <odbc-joined-table>

 

table-ref ::=

   <single-table> |

   <joined-table>

 

single-table ::=

   table-name AS correlation-name

 

odbc-joined-table ::=

   {oj <joined-table> }

 

joined-table ::=

   <single-table> LEFT OUTER JOIN <table-ref> ON join-condition

 

JOINTYPE=WATCOM

This is the join syntax used by SQL Anywhere and is a merger of the ODBC and ANSI specifications. The Base Normal Form for this syntax is:

from-clause ::=

   FROM <table-ref> |

   FROM <odbc-joined-table>

 

table-ref ::=

   <single-table> |

   <joined-table>

 

single-table ::=

   table-name AS correlation-name

 

odbc-joined-table ::=

   {oj <joined-table> }

 

joined-table ::=

   <table-ref> LEFT OUTER JOIN <single-table> ON join-condition

 

JOINTYPE=FIRSTSQL

This is the join syntax used by FirstSQL and is not recommended to be used with any other file format.

JOINTYPE=INNER

This is a format that should work with any database, but is likely to be very slow.

JOINTYPE=NONE

This indicates to perform the join on the client.