DATA Step, Macro, Functions and more

Can someone explain this SQL to me

Reply
Trusted Advisor
Posts: 1,610

Can someone explain this SQL to me

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

Respected Advisor
Posts: 4,641

Re: Can someone explain this SQL to me

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;

PG

PG
PROC Star
Posts: 7,360

Re: Can someone explain this SQL to me

Paige,

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

Art

Respected Advisor
Posts: 4,641

Re: Can someone explain this SQL to me

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;

PG

PG
Trusted Advisor
Posts: 1,610

Re: Can someone explain this SQL to me

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.

Ask a Question
Discussion stats
  • 4 replies
  • 280 views
  • 3 likes
  • 3 in conversation