Understanding the given correlated subquery
zgguy's explanation is very good. It is also illustrated nicely in the execution plan (I have used SSMS2014 to generate one):




the inner query shows on the second "row"
nested loops means that for each row in the outer query, inner input in scanned and matching records are selected
filter (=3) is applied on records from previous step