Yeah, correlated subqueries are fine. Unless you're on MySQL, which (historically, at least) was infamously terrible at executing them.

The reason this query works is because correlated subqueries... need something to correlate with! Usually the column from the outside query is only referenced in the WHERE clause, but there isn't really a reason you can't reference it in the inside query's SELECT clause. After all, what if you needed it for a CASE expression there?

There are certainly better ways to write what this query is accomplishing -- at the very least there are ways to write it that make it much more clear what your intentions are -- but just because it's weird doesn't mean it's invalid. The developers know they cannot anticipate all possible operations that might be desired, and there are already countless ways to write queries incorrectly.

As an employee at a database company working on the plan optimizer I would like to ask you not to use correlated subqueries for my own sanity. Close to 100% of the mind blowing bugs are correlated subqueries. Kidding of course but holy shit my life would be so much easier without them.

I am curious as to why. They should be represented like joins, right?

They’re slow as fuck and most of them can be rewritten to not be correlated, so we attempt to heavily rewrite them. There’s basically an entire subfield of cs research that focuses on strategies for rewriting CSQs and the papers have gotten progressively more and more complicated making them very difficult to reproduce without bugs. I’ve spent easily weeks just reading these papers let alone implementing them.

I'd love to read a deep dive on this. Database implementation internals are always very interesting.