topic Re: Can someone explain this SQL to me in SAS Programming

Can someone explain this SQL to me

PaigeMiller — Wed, 26 Feb 2014 20:21:07 GMT

I received some rather complicated SQL code from our database group. I input it into SAS PROC SQL properly, and it works. Great! But I don't understand it.

What I expect to see something like the following constructions in the FROM Clause

FROM table1 RIGHT JOIN table2 ON table1.lotid=table2.lotid

LEFT JOIN table3 ON table1.historyid=table3.historyid

What I expect to see, and the way I write code, looks like the above. The key point I am making above is that each join has its own ON clause.

But that's not what I see in this working example of SQL

I see tables being joined with no apparent ON clause in the sequence that I displayed above, note that many joins are done in sequence and only after the third join do I see an ON clause. The code below is a fragment of the entire FROM clause. Please explain this to me.

FROM db.StartHistoryDetail RIGHT OUTER JOIN db.Employee Employee_1 INNER JOIN db.Product

INNER JOIN db.ProductBase ON db.Product.ProductBaseId = db.ProductBase.ProductBaseId

INNER JOIN db.HistoryMainline INNER JOIN db.Container

ON db.HistoryMainline.ContainerId = db.Container.ContainerId

Re: Can someone explain this SQL to me

PGStats — Wed, 26 Feb 2014 23:23:06 GMT

I find it puzzling too, The following example generates an error at the missing ON clause:

data a;

do x = "A", "B", "C"; output; end; run;

data b;

do x = "R", "S"; output; end; run;

data c;

do x = "1", "2", "S"; output; end; run;

proc sql;

select * from a inner join b inner join c on b.x=c.x;

quit;

Re: Can someone explain this SQL to me

art297 — Wed, 26 Feb 2014 23:26:49 GMT

Paige,

I get an error if I run that code. Can you post the full code that actually ran?

Art

Re: Can someone explain this SQL to me

PGStats — Thu, 27 Feb 2014 03:11:21 GMT

My guess is that your query is not run by SAS, it is passed to some other DBMS as a pass-through query. That DBMS could possibly understand the joins without ON clauses as NATURAL joins. Natural joins have implicit ON clauses that equate all field pairs with corresponding names and types. Going back to my small example, an equivalent SAS/SQL query would be :

data a;

do x = "A", "B", "R"; a + 1; output; end; run;

data b;

do x = "R", "B"; b + 1; output; end; run;

data c;

do x = "1", "2", "R"; c + 1; output; end; run;

proc sql;

select * from a natural inner join b inner join c on b.x=c.x;

quit;

Re: Can someone explain this SQL to me

PaigeMiller — Thu, 27 Feb 2014 12:52:32 GMT

Yes, the SQL is passed to an SQL Server for execution.

If, as you are all telling me, that this is not a SAS issue but rasther an issue about how the code works on an SQL Server, I think the best answer for me is to go back to the database group that created the code and see if any of them can explain it.

If I get an explanation, I will post it here.