topic PROC SQL: JOIN versus WHERE Syntax in SAS Programming

PROC SQL: JOIN versus WHERE Syntax

FK21 — Fri, 14 Jun 2024 09:35:24 GMT

Hi Folks,

I have a couple of questions with respect to the following code:

data class1(drop=in2) class2 (drop=in1);
set sashelp.class (keep=name) ;
in1  = "left";
in2 = "right";
run;


PROC SQL;
create table work.join_syntax as 
select coalesce(cl1.name , cl2.name) as name, in1, in2
from work.class1 as cl1 inner join work.class2 as cl2
on cl1.name = cl2.name
;

create table work.where_syntax as 
select coalesce(cl1.name , cl2.name) as name, in1, in2
from work.class1 as cl1 , work.class2 as cl2
where cl1.name = cl2.name
;

quit;

The result table seems to be identical in terms of observations.

Does this also hold, when one or both of the tables have duplicat key values?

Also, with regards to the sort order and performance, respectivley: is it advantageous to use one over the other?

A similar question was asked with regards to EG code generation back in 2011:
https://communities.sas.com/t5/SAS-Enterprise-Guide/Join-Vs-Where/td-p/28293

Cheers,

FK21

Re: PROC SQL: JOIN versus WHERE Syntax

Kurt_Bremser — Fri, 14 Jun 2024 10:25:51 GMT

Semantically, the codes do the same and will produce the same result; multiple observations for a key value will also result in the same cartesian products.

If there is a performance difference is best determined through tests with sufficiently large datasets.

You can use the _METHOD and _TREE options of the PROC SQL statement to see how the query is handled internally.

Re: PROC SQL: JOIN versus WHERE Syntax

FK21 — Fri, 14 Jun 2024 11:34:30 GMT

Thank you, Kurt for your quick and clean answer!