• It's not entirely clear why you expect to get the 3 and 4 values twice, but you could create something like that using UNION ALL eg (using Chris' sample data)

    SELECT *

    FROM #table1 t1

    LEFT JOIN #table2 t2 ON t1.id = t2.id1

    UNION ALL

    SELECT id, value, NULL, NULL

    FROM #table1 t1

    WHERE NOT EXISTS ( SELECT * FROM #Table2 t2 WHERE t1.id = t2.id1 )