So i tried to make use of straight SQL to solve these kind of problems, ie( without the If-else logic) in T-SQL.
What i did is a left join the two tables using table1.name column with table2.name column. In the result set we desire, the values with NULL in table2.name would be ones which need to be at the bottom of the list, 

This part is done by the  "order by case when b.name is null then 2 else 1 end" . so the values for 'C' and 'F' would appear on the top of the list, followed by the null values in table2.nameThe second order by is on the table1.name field assuming you would like to see an order on the rest of the elements in table1.name (ie A,B,D,E

Next time post with DDL and DML statements, this can make it easy on others to have a easier start while solving problems.

And if you like my answer , hit the like.

create table table1(name varchar(10));

create table table2(name varchar(10));

insert into table1 values
('A'),('B'),('C'),('D'),('E'),('F');

insert into table2 values
('C'),('F');

     select a.name,b.name
       from table1 a
  left join table2 b
         on a.name=b.name
order by case when b.name is null then 2 else 1 end
          ,a.name;