Add an additional column specially for ordered, but don't select it e.g.

SELECT id, val
FROM (
  SELECT '9' AS id, 700 AS val, 1 AS SortOrder
  UNION ALL -- Always use union all where you can for performance
  SELECT s1.id, s2.val, 0 AS SortOrder
  FROM s1
  LEFT JOIN s2 ON s1.id = s2.id
) x
ORDER BY SortOrder DESC, val

Your end part should be like this for desired output.

UNION all
select *
from (
select top 100 percent s1.id,
    s2.val
from s1
 Left join s2
     on s1.id = s2.id  
order by val
) sub_query

Edit: TOP 100 percent doesn't work in newer versions. (TOP [hard coded value]) will work but it would not be a clean solution).

The order by clause needs to be applied to the final select . You can use a subquery to perform the union and then select the results of the subquery and order them. A case expression in the order by clause will force the '9' row to the top of the results.

WITH
  s1 AS ( select id from ( values ( '1' ), ( '4' ), ( '2' ), ( '3' ), ( '5' ) ) as s1v( id ) ),
  s2 AS ( select id, val from ( values ( '4', 100 ), ( '1', 500 ), ( '3', 700 ), ( '2', 800 ) ) as s2v( id, val ) )
select id, val
  from (
    select '9' as id, 700 as val -- id 9 should stay on first row
    union
    select s1.id, s2.val
      from s1 left join s2 on s1.id = s2.id
    ) as Antoine
  order by case when id = '9' then 0 else 1 end, -- Force '9' to be the first row.
    val;

dbfiddle .