Menu
Are there rules of thumb for developers when to use join instead of subquery or are they the same.
913
The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query.
Subqueries and JOIN s can both be used in a complex query to select data from multiple tables, but they do so in different ways. Sometimes you have a choice of either, but there are cases in which a subquery is the only real option.
The first principle is "State the query accurately". The second principle is "state the query simply and obviously" (which is where you usually make choices). The third is "state the query so it will process efficiently".
If its a dbms with a good query processor, equivalent query designs should should result in query plans that are the same (or at least equally efficient).
My greatest frustration upon using MySQL for the first time was how conscious I had to be to anticipate the optimizer. After long experience with Oracle, SQL Server, Informix, and other dbms products, I very seldom expected to concern myself with such issues. It's better now with newer versions of MySQL, but it's still something I end up needing to pay attention to more often than with the others.
99
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
