Passing Joins to the DBMS

When you perform a join across SAS/ACCESS librefs in a single DBMS, PROC SQL can often pass the join to the DBMS for processing. Before implementing a join, PROC SQL checks to see whether the DBMS can process the join. A comparison is made using the SAS/ACCESS LIBNAME statements for the librefs. Certain criteria must be met for the join to proceed. Select your DBMS below to view a list of the required criteria before PROC SQL can pass the join.

If it can, PROC SQL passes the join to the DBMS, which performs the join and returns only the results to SAS. If the DBMS cannot process the join, PROC SQL processes it.

The following types of joins are eligible for passing to the DBMS:

• For all DBMSs, inner joins between two or more tables.

• For DBMSs that support ANSI outer join syntax, outer joins between two or more DBMS tables.

• For ODBC and Microsoft SQL Server, outer joins between two or more tables. However, the outer joins must not be mixed with inner joins in a query.

• For DBMSs that support nonstandard outer join syntax (Informix, Oracle, and Sybase), outer joins between two or more tables, with the following restrictions:

  	Full outer joins are not supported.
  	Only a comparison operator is allowed in an ON clause. For Sybase, the only valid comparison operator is '='.
  	For Oracle and Sybase, both operands in an ON clause must reference a column name. If an operand is a literal, it cannot be passed to the DBMS. Because these DBMSs do not support this, all ON clauses are transformed into WHERE clauses before attempting to pass the join to the DBMS. This can result in queries not being passed to the DBMS if they include additional WHERE clauses or contain complex join conditions.
  	For Informix, the outer joins cannot consist of more than two tables and cannot contain a WHERE clause.
  	For Sybase: If there are multiple joins, or joins with additional WHERE clauses, to have the join processed internally by PROC SQL rather than passing it to the DBMS. This is because Sybase evaluates multi-joins with WHERE clauses differently than SAS. To allow PROC SQL to process the join internally use the SAS/ACCESS DIRECT_SQL= option.

  Note:   If PROC SQL cannot successfully pass down a complete query to the DBMS, it might try again to pass down a subquery. You can analyze the SQL that is passed to the DBMS by turning on SAS tracing options. The SAS trace information displays the exact queries being passed to the DBMS for processing.  

In the following example, two large DBMS tables named TABLE1 and TABLE2 have a column named DeptNo, and you want to retrieve the rows from an inner join of these tables where the DeptNo value in TABLE1 is equal to the DeptNo value in TABLE2. The join between two tables in the DBLIB library (which references an Oracle database) is detected by PROC SQL and passed by SAS/ACCESS directly to the DBMS. The DBMS processes the inner join between the two tables and returns only the resulting rows to SAS.

libname dblib oracle user=testuser password=testpass;
proc sql;
   select tab1.deptno, tab1.dname from
      dblib.table1 tab1,
      dblib.table2 tab2
      where tab1.deptno = tab2.deptno;
quit;

The query is passed to the DBMS, generating the following Oracle code:

select table1."deptno", table1."dname" from TABLE1, TABLE2
   where TABLE1."deptno" = TABLE2."deptno"
   

In the following example, an outer join between two Oracle tables, TABLE1 and TABLE2, is passed to the DBMS for processing.

libname myoralib oracle user=testuser password=testpass;
proc sql;
   select * from myoralib.table1 right join myoralib.table2
      on table1.x = table2.x
      where table2.x > 1;
quit;

The query is passed to the DBMS, generating the following Oracle code:

select table1."X", table2."X" from TABLE1, TABLE2
   where TABLE1."X" (+)= TABLE2."X"
   and (TABLE2."X" > 1)

Copyright © 2007 by SAS Institute Inc., Cary, NC, USA. All rights reserved.