I totally missed that spurious semicolon in the subquery.

I was focused on the logic of the statement, and not a spurious typo.

This question TOTALLY FAILS to illustrate any point about a correlated subquery. The only point this question illustrates is that I was not careful enough in my review of the statement, and that including an invalid token in a SQL statement is a syntax error.

I could get the exact same result with a misspelled a keyword.