I'm writing a query in SQL Server, and I have a task to filter first table:
| c1 | c2 | c3 | c4 |
|---|---|---|---|
| 40 | 161 | 425 | 702 |
| 40 | 161 | 719 | 791 |
| 45 | 161 | 719 | 791 |
| 45 | 700 | 719 | 791 |
| 47 | 700 | 719 | 791 |
With data from second table:
| c1 | c2 | c3 | c4 |
|---|---|---|---|
| 40 | NULL | NULL | NULL |
| 45 | 161 | NULL | NULL |
The issue is, data in second table have "wildcards" - if there's NULL in column, than it can be any integer. Long story short, filtered table should look something like this:
| c1 | c2 | c3 | c4 |
|---|---|---|---|
| 45 | 700 | 719 | 791 |
| 47 | 700 | 719 | 791 |
I can probably end up with way to filter that with loop, but it seems don't be optimised at all.
To further clarify - the second table has "mask rules" to apply to the first table. Mask rules are going from c1 to c4:
One possibility is to use NOT EXISTS and then check for your filter conditions replacing null with the value from table1 so it matches e.g.
select *
from t1
where not exists (
select 1
from t2
where t1.c1 = t2.c1
and (t1.c2 = isnull(t2.c2, t1.c2))
and (t1.c3 = isnull(t2.c3, t1.c3))
and (t1.c4 = isnull(t2.c4, t1.c4))
);
Note: This assumes that c1 cannot be null.
Returns:
| c1 | c2 | c3 | c4 |
|---|---|---|---|
| 45 | 700 | 719 | 791 |
| 47 | 700 | 719 | 791 |