Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1(columnFoo, colx, some_value) AS
SELECT 123, null, 1 FROM DUAL UNION ALL
SELECT 123, 1, 2 FROM DUAL UNION ALL
SELECT 123, 2, 2 FROM DUAL UNION ALL
SELECT 123, 3, 3 FROM DUAL UNION ALL
SELECT 123, 4, 3 FROM DUAL UNION ALL
SELECT 123, 5, 3 FROM DUAL UNION ALL
SELECT 123, 6, 4 FROM DUAL;
7 rows affected

CREATE TABLE table2(column1, colY) AS
SELECT 1, 2 FROM DUAL UNION ALL
SELECT 3, 2 FROM DUAL UNION ALL
SELECT 5, 2 FROM DUAL;
3 rows affected

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)
       );


COLUMNFOO
COLX
SOME_VALUE


123
null
1

123
1
2

123
3
3

123
4
3

123
5
3

123
6
4


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 t1.some_value THEN 1 END) > 0 
       ) t2
WHERE  t1.columnFoo = 123
AND    ( t1.some_value is null OR t2.matched = 1);


COLUMNFOO
COLX
SOME_VALUE


123
null
1

123
1
2

123
3
3

123
4
3

123
5
3

123
6
4


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 table1.some_value THEN 1 END) > 0 
          )
       );


COLUMNFOO
COLX
SOME_VALUE


123
null
1

123
1
2

123
3
3

123
4
3

123
5
3

123
6
4