Welcome to WebmasterWorld Guest from 54.226.45.241

Forum Moderators: open

Message Too Old, No Replies

Noob question: nesting inner joins

what's the difference between these two snippets

     

caribguy

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

WebmasterWorld Senior Member 5+ Year Member



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

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

WebmasterWorld Senior Member 5+ Year Member



I guess that's a no then?

LifeinAsia

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

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

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

WebmasterWorld Senior Member 5+ Year Member



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 :)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month