Common Table Extensions are often good enough:
With americans as (select ...) select from ..., americans ...
https://www.draxlr.com/blogs/common-table-expressions-and-it...
back
2 comments
The lack of variables is a major pain point for many devs when learning SQL. CTEs address one aspect. It's maybe not 1:1 or ideal, but lateral joins are a great way to get in-query variables and are extremely powerful for controlling cardinality!
Oh huh, I didn't know that was a thing. Exactly what I wanted.