Passing Joins to the DBMS

Overview of 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. Passing the JOIN operation to the target DBMS results in less data movement and improves performance compared to performing a join in SAS. When a join is performed in SAS, the data must first be loaded into SAS before the join is completed. Standard INNER, OUTER LEFT, OUTER RIGHT, OUTER FULL, and CROSS joins can be passed to the target DBMS. For more information about using PROC SQL for joins, see Retrieving Data from Multiple Tables in SAS SQL Procedure User’s Guide.

Before PROC SQL implements a join, it checks to see whether the DBMS can process the join. A comparison is made using the SAS/ACCESS LIBNAME statement for the librefs. Certain criteria must be met for the join to proceed. See the information in the DBMS-specific reference for your interface to see the criteria that are required to pass a join to the DBMS.

If PROC SQL determines that a query meets the criteria, it passes the join to the DBMS. The DBMS then performs the join and returns only the results to SAS. PROC SQL processes the join in SAS if the query cannot be passed to the DBMS.

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.

To determine whether a JOIN operation is passed to the DBMS and to diagnose issues, use the SASTRACE= option. For more information, see Tracing and Evaluating SQL Generation.

Join Examples

In this example, TABLE1 and TABLE2 are large DBMS tables. Each has a column named DeptNo. You want to retrieve the rows from an inner join of these tables. 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=myusr1 password=mypwd1;
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 TXT_1."deptno", TXT_1."dname" from TABLE1 TXT_1, TABLE2 TXT_2
   where TXT_1."deptno" = TXT_2."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=myusr1 password=mypwd1;
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 TXT_1."X", TXT_2."X" from TABLE1 TXT_1 right join TABLE2 TXT_2 on
TXT_1."X" = TXT_2."X" where TXT_2."X" > 1

Passing Joins That Use Two or More LIBNAME Statements

SAS/ACCESS can pass down JOIN operations when more than one LIBNAME statement is involved. In this case, SAS/ACCESS starts by comparing the LIBNAME statements to see whether they are equivalent. Two LIBNAME connections are equivalent when they share these attributes:

database source types
connection options, including the user ID, server, and so on

Equivalent LIBNAME connections can connect to different database instances, as long as they are the same type of database and are on the same server. If the LIBNAME statements are determined to be equivalent, then a JOIN operation that uses both LIBNAME connections can be passed to the DBMS.

Note: When you join tables across multiple LIBNAME connections, implicit pass-through uses the first connection to process the data. LIBNAME options from subsequent connections are ignored.

This example demonstrates how SAS performs an inner join operation that uses two librefs, X and Y. Libref X references the Test database, and libref Y references the Test2 database.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;
libname x netezza server='<server>.com' database=test ...;
libname y netezza server='<server>.com' database=test2 ...;

/* drop tables if already in existence */
%macro drop(table);
%if %sysfunc(exist(&table)) %then proc delete data=&table.; run;
%mend drop;

%drop(x.left1);
%drop(y.right1);

/* Create DBMS tables */
data x.left1;
   length lname $12 lcity $12;
   input keyx lname lcity;
   cards;
   1 Lewis Durham
   2 Cummings Raleigh
   2 Kent Cary
   3 Eaton Durham
   ;

data y.right1;
   length rname $12 rcity $12;
   input keyx rname rcity;
   cards;
   1 Johnston Durham
   2 Dean Cary
   2 Corrigan Raleigh
   4 Gomez Cary
   ;

/* Inner join across different LIBNAME statements */
proc sql; 
   select * 
   from x.left1 inner join y.right1 
   on left1.keyx=right1.keyx; 
quit;

This example works because the Netezza DBMS supports a cross-database JOIN operation. If the credentials for the X libref were different from the credentials for libref Y, then SAS would not consider these librefs to be equivalent and would not pass the join to the DBMS for processing.

Last updated: February 3, 2026