I realize that the syntax is outside the norm for a query - but it is simple in that it's just calling the sub-query for each row in the FROM table and in that WHERE clause comparing the RE.NO value to one that is derived from the sub-query.

That's where the true power comes in though - as that sub-query is free to do whatever kind of select/join/where it wants to!