3 ms·
How would you explain them to someone who knows the syntax but still finds them counterintuitive?
by andrenotgiant 3y ago
How would you explain them to someone who knows the syntax but still finds them counterintuitive?
- foldr 3y agoI just think of it as performing a subquery for every row of the main query.
- dagss 3y agoTo me a lateral join is the simplest construct you could have, like a for loop in a programming language. For each row in my query so far, do this other thing. Since I learned them, everything went from being a puzzle on its own, "I know what I want to do but how to express it in SQL" -- to simply being writing things out naturally..
- magicalhippo 3y agoTIL lateral joins exist, and I struggled to understand what exactly they did because for some reasons all the blog posts and whatnot were so convoluted. Then I found this[1] SO answer, which lit my bulb. We're using such subqueries a lot, often for many columns in the same child table, and we're moving to MSSQL so will definitely change to using lateral join (or cross apply as MSSQL calls it). [1]: https://stackoverflow.com/a/28550962 https://stackoverflow.com/a/28550962
- magicalhippo 3y agoSadly, it seems that CROSS APPLY in MSSQL can be slow. Not sure why, but it was ~3x slower for typical queries we have. Oh well...