When Passing Joins to the DBMS Will Fail

By default, SAS/ACCESS tries to pass certain types of SQL statements directly to the DBMS for processing. Most notable are SQL join statements that would otherwise be processed as individual queries to each data source that belonged to the join. In that instance, PROC SQL would then perform the join internally. Passing the join to the DBMS for direct processing can result in significant performance gains.
However, there are several reasons why a join statement under PROC SQL might not be passed to the DBMS for processing. In general, the success of the join depends on the nature of the SQL that was coded and the DBMS's acceptance of the generated syntax. It is also greatly influenced by the use of option settings. Here are the primary reasons why join statements might fail to be passed.
The DBMS does not accept the generated SQL syntax.
PROC SQL attempts to pass the SQL join query directly to the DBMS for processing. The DBMS can reject the syntax for any number of reasons. In this event, PROC SQL attempts to open both tables individually and perform the join internally.
The SQL query involves multiple librefs that do not share connection characteristics.
If the librefs are specified using different servers, user IDs, or any other connection options, PROC SQL does not attempt to pass the statement to the DBMS for direct processing.
Using data set options in the query
Specifying any data set option on a table that is referenced in the SQL query prohibits the statement from successfully passing to the DBMS for direct processing.
Using certain LIBNAME options
Specifying LIBNAME options that request such member-level controls as table locks (READ_LOCK_TYPE= LIBNAME Option or UPDATE_LOCK_TYPE= LIBNAME Option LIBNAME options) prohibits the statement from successfully passing to the DBMS for direct processing.
Using SAS functions on the SELECT clause
Specifying SAS functions on the SELECT clause can prevent joins from being passed.
Using the DIRECT SQL= LIBNAME option setting.
The DIRECT SQL= LIBNAME option default setting is YES. PROC SQL attempts to pass SQL joins directly to the DBMS for processing. Other settings for DIRECT_SQL= influence the nature of the SQL statements that PROC SQL tries to pass down to the DBMS or if it tries to pass anything at all.
DIRECT_SQL=YES
PROC SQL automatically attempts to pass the SQL join query to the DBMS. This is the default setting for this option. The join attempt could fail due to a DBMS return code. If this happens, PROC SQL attempts to open both tables individually and perform the join internally.
DIRECT_SQL=NO
PROC SQL does not attempt to pass SQL join queries to the DBMS. However, other SQL statements can be passed. If the MULTI_DATASRC_OPT= LIBNAME Option is in effect, the generated SQL can also be passed.
DIRECT_SQL=NONE
PROC SQL does not attempt to pass any SQL directly to the DBMS for processing.
DIRECT_SQL=NOWHERE
PROC SQL attempts to pass SQL to the DBMS including SQL joins. However, it does not pass any WHERE clauses associated with the SQL statement. This causes any join that is attempted with direct processing to fail.
DIRECT_SQL=NOFUNCTIONS
PROC SQL does not pass any statements in which any function is present to the DBMS. Normally PROC SQL attempts to pass down any functions coded in the SQL to the DBMS, provided the DBMS supports the given function.
DIRECT_SQL=NOGENSQL
PROC SQL does not attempt to pass SQL join queries to the DBMS. Other SQL statements can be passed down, however. If the MULTI_DATASRC_OPT= LIBNAME Option is in effect, the generated SQL can be passed.
DIRECT_SQL=NOMULTOUTJOINS
PROC SQL does not attempt to pass any multiple outer joins to the DBMS for direct processing. Other SQL statements can be passed, however, including portions of a multiple outer join.