BookmarkSubscribeRSS Feed
hulst
Fluorite | Level 6

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;

4 REPLIES 4
hulst
Fluorite | Level 6

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

Patrick
Opal | Level 21

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

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

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Patrick
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1002 views
  • 0 likes
  • 3 in conversation