What’s worse a "Where" or "Join"? Which is more expensive?

Asked

Viewed 187 times

-1

What’s worse a where or join? Which is more expensive?

sql database performance

  • Add context to your question.

  • -1 "which is more costful" ????????

  • a (des)like ;)

  • 1

    you better give a negative

  • if you are referring to FROM tabA JOIN tabB on tabA.id - tabB.id and FROM tabA, tabB WHERE tabA.id = tabB.id, both are JOINS, however the first is explicit and the second is implicit, must generate the same execution plan, so the same performance, but the explicit has the advantage of being more legible.

  • Did the answer solve your problem? Do you think you can accept it? If you don’t know how you do it, check out [tour]. This would help a lot to indicate that the solution was useful to you and to give an indication that there was a satisfactory solution. You can also vote on any question or answer you find useful on the entire site (when you have 15 points).

Show 1 more comment

1 answer

4

Deep down a join will work through searches the way the where is done. Like this where will be mounted depends on the SQL parser of the database in question. The analyzer can be better or worse according to the vendor and this can give better or worse optimizations depending on the situation. There is no magic.

A where simple certainly is less costly. A where that makes relationships between two or more tables may have the same cost of join, depends on how it is written. If the programmer is very smart and the situation allows it may make a where that does the same as a certain join would do more efficiently. If the programmer is not so good, have a good chance to write a where to obtain the same result that is worse than a join.

If you have a specific case, write with join and writes another with where, Take the test and come to a conclusion, it’s the only way to ensure this. Even if you have a lot of experience and believe that you have good intuition (who is really experienced I never believe it), you should still not trust her, you should test.

Send a command excute plan, explain or something like that.

This example will likely produce equal results in most databases:

SELECT * FROM a, b WHERE a.ID = b.ID;
 
SELECT * FROM a JOIN b ON a.ID = b.ID;

I put in the Github for future reference.

In a better context the answer could be better contextualized, but overall this is it.

  • Actually the doubt is here: - In a stored Procedure, it performs a relatively large query with some joins and aggregated wheres, which would be BETTER, add a JOIN in this query by connecting another table, or increase the conditions of the WHERE?

  • 1

    The question you asked is not in it, I answered what was asked. Even with this information, it doesn’t help much, in fact maybe even hinder because it tries to give an example, but without being concrete. There is no way for me or anyone else to guess which is better. In my answer it says this.

Browser other questions tagged sql database performance

You are not signed in. Login or sign up in order to post.