CTE joining the same table twice

Solution 1:

As suggested by Nikola Markovinović in his comment:

SELECT t.Position, c.Position, t.Base, c.Base FROM (select * from @MitoT t where MitoResultID = @ResT) t FULL OUTER JOIN (select * from @MitoT c where MitoResultID = @ResC) c ON t.Position = c.Position

This works perfectly and CTE now looks like:

WITH Compare (PositionT, PositionC, BaseT, BaseC) AS (
    SELECT t.Position, c.Position, t.Base, c.Base 
        FROM (select * from @MitoT t where MitoResultID = @ResT) t 
        FULL OUTER JOIN (select * from @MitoT c where MitoResultID = @ResC) c 
        ON t.Position = c.Position
    )
SELECT * FROM Compare

with resulting table: