In online forums, I am observing one bad query practice. People are using multiple tables in the SELECT query by separating using commas.
Bad Query Like:
SELECT * FROM a, b, c WHERE a.id = b.id AND b.id = c.id;
Many of the Database Professionals are thinking that It is working like JOINs.
I am writing this article in the “Database Designing” category, means It applies to all database professionals for knowing the truth of this kind of bad SQL Query.
When you write a query like above sample, internally Query Planner creates a virtual join between these tables and this is not a strait forward joins creation. A Query Planner has to check all possible combinations like: first join A TO B and then join C, join A TO C and then join B, join B TO C and then join A.
When you write such types of bad queries, Query Planner has to do additional work for joining the tables correctly and return the correct result.
But actually It checks the data by creating a full Cartesian Product between these tables which is very costly and slow down the query performance.
We have taken example of three tables only, now let’s assume if we have 10 tables and people wrote a SELECT query using comma separated tables.
So please I am requesting, never use such a bad query and try to write explicit JOINs in the SELECT Query.
Another interesting point is:
When I discussed this point with our team, one of database developers said It is good that Query Planner is deciding the order of JOINS. If we give explicit joins, It may not be in the correct order all the times so let’s Query Planner to do.
My simple answer was:
I am 5% agree with you, but do not forget that we are enough professional and can define the JOINs order correctly. We should not rely on Query Planner because It requires more internal I/O to just prepare a joins of the table.
Correct Query should like:
SELECT a.* FROM a JOIN b ON a.id=b.id JOIN c ON b.id=c.id
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.