CODEWITHSUNDEEP
sqlpostgresql
Aayush Karki
Aayush Karki

Reputation: 841

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, 
       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.

Upvotes: 20

Views: 80648

Answers (4)

suriruler
suriruler

Reputation: 109

select a.name,
    case
        when a.name in (select distinct name from table2) then 'common'
        else 'not common'
    end as new
from table1 a

Upvotes: 0

Anuraag Veerapaneni
Anuraag Veerapaneni

Reputation: 679

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

Upvotes: 10

fthiella
fthiella

Reputation: 49089

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

Upvotes: 26

sle1306
sle1306

Reputation: 134

Just replace the in with from in your subquery.

Upvotes: 1

Related Questions