Optimizing Your SQL Usage When Passing Joins to the DBMS Will Fail 35
for Oracle, ODBC, Microsoft SQL Server, and SYBASE (which have nonstandard
outer join syntax), outer joins between two or more tables, with the following
restrictions:
ODBC and Microsoft SQL Server
The outer joins must not be mixed with inner joins in a query.
Oracle
There are no restrictions.
SYBASE
There must be no WHERE clause.
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)
When Passing Joins to the DBMS Will Fail
SAS/ACCESS will, by default, attempt to pass certain types of SQL statements
directly to the DBMS for processing. Most notable are SQL join statements that
otherwise would be processed as individual queries to each data source that belonged to
the join. In that instance, the join would then be performed internally by PROC SQL.
Passing the join to the DBMS for direct processing can result in significant performance
gains.
