What is worse than where
or join
? Which is more costly?
What is worse than where
or join
? Which is more costly?
In the background a join
will work by searching the way where
is done. How 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.
It depends on how it is written. A simple% of% is certainly less costly. A where
that makes relationships between two or more tables may have the same cost as where
, depending on how it is written. If the programmer is very clever and the situation allows, he can make a join
that does the same thing that a certain where
would do more efficiently. If the programmer is not so good, there is a good chance he will write join
to get the same result that is worse than where
.
If you have a concrete case, write with join
and write another with join
, take the test and come to a conclusion. It's the only way to guarantee this. Even if you have a lot of experience and believe you have good intuition (you are experienced really do not believe it), you still should not trust it, you should test.
Send a command where
, excute plan
or something like this.
This example will likely produce the same results for most databases:
SELECT * FROM a, b WHERE a.ID = b.ID;
SELECT * FROM a JOIN b ON a.ID = b.ID;
In a better context the answer could be better contextualized, but overall this is it.