3 ms·
The subquery would be in the WHERE clause, and it would be called for each row, thus making it N times slower than the JOIN syntax. There is a reason why this
by Svip 8y ago
The subquery would be in the WHERE clause, and it would be called for each row, thus making it N times slower than the JOIN syntax. There is a reason why this style has been abandoned.
- kijin 8y agoModern RDBMS's are smart enough to figure out when the result of a subquery doesn't depend on rows outside of it. Then the subquery is run only once, and its result is treated as a temporary table to be JOINed.
- Svip 8y agoFor most simple queries, that is correct. But if the query gets complicated, and/or the tables involved have several indexes, where several might be useful, then it might make the wrong decision. I've written plenty of complicated queries, where I have to help the query optimiser along. Although, the most modern I've had this experience with is SQL Server 2014, but that definitely still needed guiding a few times.