Can "value in list or list is empty" be written shorter?-CodePudding

Given this SQL:

select * from table1
           where
                 table1.columnFoo = 123
              and
               (
                 some_value is null
               or
                 some_value in (select column1 from table2 where table1.colX=table2.colY)
               or
                    not exists (select column1 from table2 where table1.colX=table2.colY)
               );

-- some_value is a constant or an input parameter in an (PL/)SQL procedure
-- if it is non null, then we want to filter by it. Except if the list selection is empty.

Is there a way to write the "in list or list is empty" part shorter? Preferably in a way that contains the list only once (see the Don't_repeat_yourself principle )

I'm interested for Oracle SQL or PL/SQL, but other information is also welcome.

As requested, a MRE that works in SQL*Plus:

create table table1 as select 1 id, 'one' name , 12 price from dual
                 union select 2   , 'two'      , 22       from dual
                 union select 3   , 'thr'      , 33       from dual;

create table table2 as select 1 id1, 88 idX, sysdate-1 validDate from dual -- valid
                 union select 1    , 99    , sysdate 2           from dual -- these two are not valid (yet)
                 union select 2    , 99    , sysdate 3           from dual;

var some_value number
--exec :some_value  := 3  --   uncomment for non null values

with cte as (select id1,idX from table2 where validDate<sysdate)
select * from table1
       where
             table1.price  > 10
          and
           (
             :some_value is null
           or
             :some_value in (select idX from cte where table1.id=cte.id1)
           or
                 not exists (select idX from cte where table1.id=cte.id1)
           );

CodePudding user response：

From Oracle 12, you could use a LATERAL join with conditional aggregation:

SELECT t1.*
FROM   table1 t1
       CROSS JOIN LATERAL(
         SELECT 1 AS matched
         FROM   table2 t2
         WHERE  t1.colX=t2.colY
         HAVING COUNT(*) = 0
         OR     COUNT(CASE t2.column1 WHEN :some_value THEN 1 END) > 0 
       ) t2
WHERE  t1.columnFoo = 123
AND    ( :some_value is null OR t2.matched = 1);

Or a similar technique using EXISTS:

select *
from   table1
WHERE  columnFoo = 123
AND    (  :some_value is null
       OR EXISTS(
            SELECT 1
            FROM   table2
            WHERE  table1.colX=colY
            HAVING COUNT(*) = 0
            OR     COUNT(CASE column1 WHEN :some_value THEN 1 END) > 0 
          )
       );

db<>fiddle here