WITH
    Table_1 (_name, _site, attr) AS
    (
        SELECT *
        FROM
        (
            VALUES
                ('OOO 1', '1.ru', NULL),
                ('OOO 2', '2.ru', NULL)
        ) AS t1 (_name, _site, attr)
    ),
    Table_2 (_name, _site, attr) AS
    (
        SELECT *
        FROM
        (
            VALUES
                ('OOO 1', '1.ru', 'attr_1'),
                ('OOO 2', '2.ru', 'attr_2')
        ) AS t1 (_name, _site, attr)
    ),
    Main AS
    (
        SELECT t1._name, t1._site
            , ISNULL
            (
                t1.attr
                , (
                    SELECT attr
                    FROM Table_2
                    WHERE t1._name = _name
                        AND t1._site = _site
                )
            ) AS attr

        FROM Table_1 AS t1
    )

SELECT *
FROM Main