Menu
Is there any difference in terms of performance between these two versions of the same query?
--Version 1 SELECT p.Name, s.OrderQty FROM Product p INNER JOIN SalesOrderDetail s on p.ProductID = s.ProductID --Version 2 SELECT p.Name, s.OrderQty FROM Product p INNER JOIN (SELECT ProductID, OrderQty FROM SalesOrderDetail) s on p.ProductID = s.ProductID I've heard it said (by a DBA) that Version 2 is faster because it fetches, within the inner SELECT statement, only the columns that are required for the query. But that doesn't seem to make sense, since query performance (as I know) is based on number of rows affected and final list of columns returned.
The query plans for both are identical, so I'm guessing there isn't any difference between the two.
Am I correct?
986
SQL INNER JOIN Example Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!
Which one will be faster probably depends on the size of tables - if TABLE1 has very few rows, then IN has a chance for being faster, while JOIN will likely be faster in all other cases. This is a peculiarity of MySQL's query optimizer.
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.
I won't leave you in suspense, between Joins and Subqueries, joins tend to execute faster. In fact, query retrieval time using joins will almost always outperform one that employs a subquery. The reason is that joins mitigate the processing burden on the database by replacing multiple queries with one join query.
You are correct. You did exactly the right thing, checking the query plan rather than trying to second-guess the optimiser. :-)
88
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
