• After some years of observation I found if 2 queries are similar the optimizer ill process both in a similar way.

    It means its likely there's no difference in performance for most joins x sub-queries scenarios.

    And also it's mean

    If you found differente query plans it's likely the queries are different and that's the case here.

    Put yourself in the compiler place.

    You can read the second query as:

    fetch manager-1 (a single clustered index seek)

    now fetch me all employers managed by manager-1 (40k index seek)

    First query reads as:

    fetch me all employers managed by manager-1 (40k index seek)--same cost as the other way

    and for each fetched employer seek if there's a manager for him and fetch it as well.

    Since its not a strict inner join its possible there are rows where the id is null.

    The compiler still try (futile) to optimize by using a lazy spool.

    I cannot test by now but I guess a FK can improve the subquery.