3 ms·
Also easy to do this with a lateral join (and a limit 1 in the subquery).
by foldr 3y ago
Also easy to do this with a lateral join (and a limit 1 in the subquery).
- giraffe_lady 3y agoIf you can do a lateral join off the top of your head this isn't the kind of question you need to have a strategy for anyway.
- foldr 3y agoI 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...
- mwexler 3y agoFwir, BigQuery doesn't yet support lateral joins, though they do use the same concept in their UNNEST and array expansion features. It would be nice to have this feature; its a handy hammer for the right type of nail.