topic Query Builder Joins are actually where clause statements? in SAS Enterprise Guide

Query Builder Joins are actually where clause statements?

hulst — Fri, 11 Oct 2013 09:36:33 GMT

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;

Re: Query Builder Joins are actually where clause statements?

hulst — Fri, 11 Oct 2013 09:52:07 GMT

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... ???

Re: Query Builder Joins are actually where clause statements?

Patrick — Fri, 11 Oct 2013 10:27:11 GMT

I believe these are just 2 notations for inner joins: Implicit and Explicit.

http://en.wikipedia.org/wiki/Join_(SQL)

Re: Query Builder Joins are actually where clause statements?

ChrisHemedinger — Fri, 11 Oct 2013 12:23:02 GMT

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

Re: Query Builder Joins are actually where clause statements?

Patrick — Fri, 11 Oct 2013 12:41:42 GMT

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.