How can I use COALESCE to simulate "IF/ELSE" inside WHERE IN for multiple values?-CodePudding

How can I achieve this with COALESCE, or something else?

I found out about COALESCE here, but this approach is different, as I am not joining a table.

Case won't work because it cannot return multiple values.

select * 
from data
where fieldId in (

    --- IF there are IDs here, use this ---

    select fieldId 
    from relationNameFromField
    where relationSettingId in (

        select rs.id 
        from relationSetting rs
            left join entity e on e.id = rs.entityId
        where e.uid = 'R0izCPXH46'

    )

    --- ELSE use this when the above returns nothing ---

    select id from field

)

CodePudding user response：

If you can use cte then

with 
cte as(select fieldId 
    from relationNameFromField
    where relationSettingId in (

        select rs.id 
        from relationSetting rs
            left join entity e on e.id = rs.entityId
        where e.uid = 'R0izCPXH46'
),
cte1 as 
(select id from field where (select count(*) from cte) = 0),
cte2 as
(select fieldid from cte
union all
select id from cte1)
select * 
from data
join cte2 on fieldId = cte2.id;