10-11-2013 05:36 AM
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
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;
10-11-2013 05:52 AM
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... ???
10-11-2013 08:23 AM
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.
10-11-2013 08:41 AM
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.