SQL query in one statement or many?

I'm Analyzing / Reverse Engineering some query statements already written. I see it's currently being done in stages, where first there's a SELECT into a table (a permanent table but only used for temporary use as far as I can tell), then there's a second SELECT into another (permanent temporary) table, pulling data from the first table, joining with other tables. Then there's a third SELECT statement, pulling data from the second table joining with other tables. Then there's some filtering out of rows from that third temporary table, and finally the procedure creats the output table we wanted.

Question 1: In theory, is it always possible to fetch any table using just one massive SELECT statement?

Question 2: Would a single massive SELECT statement be any better? Faster? Easier or more difficult to maintain and document?
deleydSoftware EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanti PrasadCommented:
Hi

Q1 =  fetching any table is done by a Select statement only

Q2
If you need one column then select column1 from tablename  will be faster than Select * from tablename.
then to make it still faster if you use Where clause then it will still be faster as you will have a less record set.
Adding index will make it still  faster

So optimizing of queries are done in many ways and the below link has some info to help you

http://hungred.com/useful-information/ways-optimize-sql-queries/
Phil DavidsonCommented:
Question 1: In theory, is it always possible to fetch any table using just one massive SELECT statement?

One select statement can retrieve data from multiple tables.  If you MySQL instance is configured to do so, one select statement can retrieve data from tables in different databases on different tables.  Join statements would be used.  Federated databases would be configured before this would happen.


Question 2: Would a single massive SELECT statement be any better? Faster? Easier or more difficult to maintain and document?

Better?  That depends on many factors.  

Faster?  It could be faster or slower.  It could increase reads and writes to the same table or tables if you eliminate the intermediate table step.  This could lead to increased I/O contention (more transactions on certain parts of the disk and/or more locking).  It could be faster without intermediate writes to the table in the first stage (if this step is not necessary).  But it isn't clear if such an optimization will be possible.  If so, you may explore this method.  

It may be more difficult to maintain and document.  I like temporary tables personally when I write SQL code.   If there is an error, you see where it failed in smaller, discrete steps.

But, I must admit it could also be easier to maintain and document (it is all on one place, without different stages).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.