3 ms·
I think everyone tends to learn their own subset of SQL. I was unaware of some of the more 'obvious' solutions in the article (e.g. I had only a very vague idea
by foldr 3y ago
I think everyone tends to learn their own subset of SQL. I was unaware of some of the more 'obvious' solutions in the article (e.g. I had only a very vague idea of how window functions work), but I use lateral joins all the time.
The nice thing about lateral joins is that they're very easy to understand conceptually once you get over the weird syntax.
- andrenotgiant 3y agoHow 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...