That step is necessary in the loop, and not in the CTE. Otherwise, a repeating hierarchy level causes an error in the loop.

For example, insert into a hierarchy table:

ID 1, Parent 2

ID 2, Parent 1

or

ID 1, Parent 1

In either case, the loop without the Not In (or a left join), will cause an error as it hits the max recursion level. The CTE doesn't have that problem and doesn't need the code.

For an example of how this can happen:

Joe is a manager of a sales department

Joe is also a salesman in that department

In this case, it is necessary for Joe to be listed as the manager, since that determines his "sales manager" commission, and it is necessary for him to be listed as a salesperson, since that determines his direct sales commissions, and his own sales count for calculating his manager commission. Separating him into two entities with two separate IDs would necessitate giving him two separate paychecks, and would thus mess up income tax, Social Security, Medicare, 401(k), etc., calculations.

A loop without controls for that kind of thing will go into an infinite loop and will error out. (Yes, I know, real life isn't as neat and tidy like we'd like it to be.)

I've tested the above scenario in CTEs, and they don't error out and do return the correct hierarchy.

If you can guarantee that the hierarchy will never include recursive elements, then you don't need that step in the loop.

The loop without that step, on data pre-tested for non-recursion, is still slower than the CTE on the same data, in my tests.