WHERE IN return all rows from another query when CASE is false-CodePudding

How can I replace this...

where fieldId in (
        select fieldId
        from setting
        where settingId = joinedTable.settingId
    )

with this...

where fieldId in (
        select id
        from field
    )

when the first where in returns nothing?

case won't work because it return multiple rows... This is the idea.

where fieldId in (
        case 
            when (select count(id) from setting where settingId = joinedTable.settingId) > 0
            then (select fieldId from setting where settingId = joinedTable.settingId)
            else (select id from field)
        end
    )

CodePudding user response：

Something like:

select distinct coalesce(setting.fieldId,field.id)
from field
left join setting on setting_id = joinedTable.settingId

If there are no matching rows in setting, one row will be produced for each row in field. left joins where the joined table is not found produce a null for all columns, so the coalesce will return each field.id.

If there are matching rows in setting, this will do a cartesian product of the two tables, with every possible combination of setting.fieldId and field.id. I'm assuming fieldId will never be null, so the coalesce will return it, not field.id, and the duplicates (one for each row in field) will be removed by the distinct.

CodePudding user response：

use OR in the where condition

where (
    fieldId in (
        select fieldId
        from setting
        where settingId = joinedTable.settingId
    ) 
    OR
    fieldId in (
        select id
        from field
    ) 
)