postgres case statement with subquery

Question

I have a subquery like this

with subquery as (select host from table_A where << some condition >>)

and in my main query, I am querying data from another table called table_B, and one of the columns is called destination_host. Now I need to check if the destination_host is in the list returned from my subquery, then I want to output TypeA in my select statement or else TypeB. My select statement looks something like

select name, place, destination_host
from table_B 
where <<some condition>>

I want to output a fourth column that is based on a condition check, let's say we call this host_category and if the destination_host value exists in the subquery then I want to add value typeA or else typeB. Please can you help me understand how to write this. I understand that it is hard to provide guidance if you don't have actual data to work with.

I tried using case statements such as this one:

when (destination_host in (select host from subquery)) THEN 'typeA' 
when (destination_host not in (select host from subquery)) THEN 'typeB' 
end as host_category

but I don't think this is the way to solve this problem.

Laurenz Albe · Accepted Answer · 2021-07-12 07:56:16Z

4

I would use EXISTS:

WITH subquery AS (...)
SELECT CASE WHEN EXISTS (SELECT 1 FROM subquery
                         WHERE subquery.host = table_b.destination_host)
            THEN 'typeA'
            ELSE 'typeB'
       END
FROM table_b;

With queries like that, you have to take care of NULL values. If table_b.destination_host is NULL, the row will always show up as typeB, because NULL = NULL is not TRUE in SQL.

answered Jul 12, 2021 at 7:56

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vik G Over a year ago

Thanks Laurenz, I will give this a try and let you know how I go

Collectives™ on Stack Overflow

postgres case statement with subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related