checking if a value exists in another table within the SELECT clause

I would use EXIST instead of IN:

select
  A.name, 
  CASE
    WHEN EXISTS (select *
                 from table2 B
                 where B.name = A.name)
    THEN 'common'
    ELSE 'not common'
  END
from
  table1 A

Using subquery in SELECT CASE will cost more. Use left join instead like below

    select A.name, 
       CASE WHEN B.name IS NOT NULL
       THEN 'common'
       ELSE 'not common'
       END

    from table1 A
    left join table2 B
    on A.name = B.name