[Stasis5001]

Sure, I forgot the self-join case. In my experience these are a minority of joins, but definitely. In postgres at least, a lot of self-joins can be solved with a DISTINCT ON instead.

Sub-selection with a FROM clause, yeah, those definitely need names. But those don't have perfectly good names that everybody already knows, whereas tables do. If you're maintaining somebody's code and they declare

  foo_bar = 10

I think it'd be pretty strange to ever consider writing

  fb = foo_bar

just because you find foo_bar too long to write or something.