02-26-2014 03:21 PM
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
02-26-2014 06:23 PM
I find it puzzling too, The following example generates an error at the missing ON clause:
do x = "A", "B", "C"; output; end; run;
do x = "R", "S"; output; end; run;
do x = "1", "2", "S"; output; end; run;
select * from a inner join b inner join c on b.x=c.x;
02-26-2014 10:11 PM
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 :
do x = "A", "B", "R"; a + 1; output; end; run;
do x = "R", "B"; b + 1; output; end; run;
do x = "1", "2", "R"; c + 1; output; end; run;
select * from a natural inner join b inner join c on b.x=c.x;
02-27-2014 07:52 AM
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.