Checking If A Value Exists In Another Table Within The Select Clause

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,

Solution 1:

I would use EXIST instead of IN:

select
  A.name, 
  CASEWHENEXISTS (select*from table2 B
                 where B.name = A.name)
    THEN'common'ELSE'not common'ENDfrom
  table1 A

Solution 2:

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

select A.name, 
       CASEWHEN B.name ISNOTNULLTHEN'common'ELSE'not common'ENDfrom table1 A
    leftjoin table2 B
    on A.name = B.name

Solution 3:

Just replace the in with from in your subquery.

Solution 4:

select a.name,
    casewhen a.name in (selectdistinct name from table2) then'common'else'not common'endasnewfrom table1 a