Do I have a wrong setting in the EG or query builder objects?
Or does it internally not matter with SAS and the SQL performance?
Why does querybuilder generate SQL code that makes the table joins into
select columns
from table1 t1, table2 t2, table3 t3, table4 t4, table5 t5
where (t1.id = t2.id and t1.id = t3.id and t1.id = t4.id and t1.id = t5.id);
Instead of using
select columns from table1
join table2 on table1.id = table2.id
join table3 on table1.id = table3.id
join table4 on table1.id = table4.id
join table5 on table1.id = table5.id;
And the menu "Join Tables" shows Join Order with text like
INNER JOIN: t1.id = t2.id
INNER JOIN: t1.id = t3.id
INNER JOIN: t1.id = t4.id
INNER JOIN: t1.id = t5.id
But "Preview -> Code" show the where clause is used... ???
I believe these are just 2 notations for inner joins: Implicit and Explicit.
EG generates different JOIN syntax for certain conditions because it allows "pass through" or "push down" to occur. PROC SQL will try to optimize the query so that it can push as much work as possible to the database (if you're working with a database). Certain constructs push down more successfully, including these "implied joins" where the tables are listed in the FROM clause separated by commas.
Chris
Thanks Chris
It's always very interesting to learn about the reasons why things are done the way they are done. Good to know when I code my own joins.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.