Desktop productivity for business analysts and programmers

Query Builder Joins are actually where clause statements?

Reply
Occasional Contributor
Posts: 6

Query Builder Joins are actually where clause statements?

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;

Occasional Contributor
Posts: 6

Re: Query Builder Joins are actually where clause statements?

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

Respected Advisor
Posts: 3,831

Re: Query Builder Joins are actually where clause statements?

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

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

Community Manager
Posts: 2,693

Re: Query Builder Joins are actually where clause statements?

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

Respected Advisor
Posts: 3,831

Re: Query Builder Joins are actually where clause statements?

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.

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