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

Question

I want to query names from table1 and also find if a name exists in table2. I have the following query but it doesn't seem to work. Any suggestions what I did wrong?

select A.name, 
       CASE WHEN A.name in (select B.name in table2 B)
       THEN 'common'
       ELSE 'not common'
       END

from table1 A

Please note that I have to get "common" / "uncommon" from the select clause itself. I'm using postgres.

Answer 1

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