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 |