Assume that the
widgets_population table records every widget which has rolled off the assembly line, while the
widgets_tested table is the subset that have been tested.
For every widget in the population, we want to determine the most recent test.
The two queries below show two different approaches to the solution.
However, the first query runs like lightning, whereas the second query seems to spin forever. Why? Is there something obviously inferior about the second query, or is this just one of those optimizer flukes?
We realize that having an inequality in the join predicate is not optimal because it can't take advantage of pre-ordering and merging, but it seems to us that the correlated subquery is just as taxing because it has to run the inner query for every row of the outer query. So, to our minds, both queries should be equally inefficient.
SELECT A.serial_ID, (SELECT MAX(B.serial_ID) FROM widgets_tested AS B WHERE A.serial_ID >= B.serial_ID) AS [ID of most recent test] FROM widgets_population AS A ; SELECT A.serial_ID, MAX(B.serial_ID) AS [ID of most recent test] FROM widgets_population AS A INNER JOIN widgets_tested AS B ON A.serial_ID >= B.serial_ID GROUP BY A.serial_ID ;
Note: It may not be possible to give more information about the DDL statements of the relevant tables and execution plans because the
widgets tables are just sanitized stand-ins for the original tables. We just wanted to ask the community if there is something obviously wacky or inefficient about the logical structure of the second query.