homepage Welcome to WebmasterWorld Guest from 54.163.91.250
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Noob question: nesting inner joins
what's the difference between these two snippets
caribguy




msg:4287920
 7:45 pm on Mar 26, 2011 (gmt 0)

Looking at EXPLAIN in MySQL workbench, the two snippets below seem to be equal.

The first piece of code is much more readable, the second is something that I adapted from an old MS Access query.

Can any of you please hit me with a cluestick and explain if there are any differences?


select transactions.transactions_id, transaction_dates.date, 
concat_ws(' - ', customers.customer_username, account_types.account_name) as accounts_info,
transaction_types.transaction_type_name, transactions.transaction_amount
from transactions

inner join transaction_types on transactions.transaction_types_transaction_types_id = transaction_types.transaction_types_id
inner join accounts on transactions.accounts_accounts_id = accounts.accounts_id
inner join transaction_dates on transactions.transaction_dates_date_id = transaction_dates.date_id
inner join account_types on accounts.account_types_account_types_id = account_types.account_types_id
inner join customers on customers.customers_id = accounts.customers_customers_id

order by transaction_dates.date desc, accounts_info asc

----

select transactions.transactions_id, transaction_dates.date,
concat_ws(' - ', customers.customer_username, account_types.account_name) as accounts_info,
transaction_types.transaction_type_name, transactions.transaction_amount
from transaction_dates

inner join ( customers
inner join ( account_types
inner join ( transaction_types
inner join ( transactions
inner join accounts on transactions.accounts_accounts_id = accounts.accounts_id
) on transactions.transaction_types_transaction_types_id = transaction_types.transaction_types_id
) on accounts.account_types_account_types_id = account_types.account_types_id
) on customers.customers_id = accounts.customers_customers_id
) on transactions.transaction_dates_date_id = transaction_dates.date_id

order by transaction_dates.date desc, accounts_info asc

 

caribguy




msg:4290877
 6:36 pm on Apr 1, 2011 (gmt 0)

I guess that's a no then?

LifeinAsia




msg:4290984
 9:02 pm on Apr 1, 2011 (gmt 0)

Offhand, I don't see much difference, other than the second being a lot more clunky. My guess would be that the second would be more inefficient. But that's just a guess, and your DB probably makes it more efficient internally.

caribguy




msg:4290989
 9:19 pm on Apr 1, 2011 (gmt 0)

Thanks for confirming that. And yes, I spent quite some time adding a new joined column to the 2nd query before scrapping the whole mess.

When I saw that the result of the 1st query was the same, I figured that 'there must be some reason' for nesting the joins. Maybe speed, memory usage?

This is a project that I really don't want to screw up, so better check with the DB gurus :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved