Optimizing Your SQL Usage

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 to see the criteria that it requires before PROC SQL can pass the join.

If it is able, PROC SQL passes the join to the DBMS. The DBMS then performs the join and returns only the results to SAS. PROC SQL processes the join if the DBMS cannot.

These 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 such DBMSs as Informix, Oracle, and Sybase that support nonstandard outer join syntax, outer joins between two or more tables with these 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. A literal operand cannot be passed to the DBMS. Because these DBMSs do not support this, all ON clauses are transformed into WHERE clauses before trying 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, outer joins can neither consist of more than two tables nor contain a WHERE clause.
	Sybase evaluates multijoins with WHERE clauses differently than SAS. Therefore, instead of passing multiple joins or joins with additional WHERE clauses to the DBMS, use the SAS/ACCESS DIRECT_SQL= LIBNAME option to allow PROC SQL to process the join internally.

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 that are being passed to the DBMS for processing. [cautionend]

In this 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. PROC SQL detects the join between two tables in the DBLIB library (which references an Oracle database), and SAS/ACCESS passes the join 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 and generates this Oracle code:

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

In this 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 and generates this Oracle code:

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

Top of Page