4 ms·
How would you solve a left join like this with that kind of syntax? LEFT JOIN ( inner join table2 ON table2.ID = table1.table2id and table2.othertype = 1234
by jontro 8y ago
How would you solve a left join like this with that kind of syntax?
LEFT JOIN (
inner join table2 ON table2.ID = table1.table2id
and table2.othertype = 1234
) on table1.outerid = outertable.Id
- Svip 8y agoThe 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.
- snidane 7y agoHow about select * from outertable, (select *, outerid from table1,table2 where table1.table2id = table2.ID and table2.othertype = 1234) t12 where outertable.Id =\ t12.outerid