sqlsql-serverjoinfilteringsql-server-2016

Filter with join where null is wildcard


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:


Solution

  • 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

    DBFiddle