T-SQL: How to return a result set that excludes values based on an Exclusion table

Question

I'm using SQL Server 2016 and I want to return a result set which has been filtered based on an "Exclusion" table. The rows in exclusion table could contain a [Make] code, a [Color] code or a combination of [Make] & [Color] codes.

The data table has approx 450K rows.

Data table:
|  Id  |  Make       |   Color      |   
x------|-------------|--------------|
|   1  |  Audi       | Blue         | 
|   2  |  Audi       | Red          | 
|   3  |  Audi       | Grey         | 
|   4  |  BMW        | Black        |
|   5  |  BMW        | Red          |
|   6  |  BMW        | White        |
|   7  |  BMW        | Grey         |
|   8  |  Ford       | Red          |
|   9  |  Ford       | Grey         |

    
Exclusion table:
|  Id  |  Make       |   Color      |   
|------|-------------|--------------|
|   1  |  Ford       | NULL         | 
|   2  |  Audi       | Red          |
|   3  |  NULL       | Grey         |

NULL values in the above table should be treated like wildcards eg. for ExclusionID = 1, return all rows where [Make] = "Ford" regardless of the [Color] attribute. The Exclusion table will never contain two NULL values in any given row.

Results table:
|  Id  |  Make       |   Color      |   
|------|-------------|--------------|
|   1  |  Audi       | Blue         |  
|   4  |  BMW        | Black        |
|   5  |  BMW        | Red          |
|   6  |  BMW        | White        |

The only solution that I'm able to come up with is to populate the Exclusion table using the ID's of all the products which must be excluded. However, this would create an exclusion table that consists of many rows which would not be very readable (I'd like to be able to readily identify which [Make] and/or [Model] codes are in the exclusion list).

Any ideas? Thanks in advance.

(edit: I have removed references to Parent & Child codes as this was not correct)

Answer 1

you can also use left join /is null method

select
dt.*
from
dbo.DataTable dt
left join 
dbo.ExclusionTable et
on 
isnull(et.Make, dt.Make) = dt.Make
and isnull(et.Color, dt.Color) = dt.Color 
where
et.make is null 
and et.color is  null

Answer 2

Here's a working example with the results you specified:

create table DataTable
(   Id int not null IDENTITY(1, 1) primary key
,   Make varchar(10)
,   Color varchar(25)
)

insert
    dbo.DataTable
(   Make
,   Color
)
values
(   'Audi',  'Blue'),
(   'Audi',  'Red'),
(   'Audi',  'Grey'),
(   'BMW',   'Black'),
(   'BMW',   'Red'),
(   'BMW',   'White'),
(   'BMW',   'Grey'),
(   'Ford',  'Red'),
(   'Ford',  'Grey')

create table ExclusionTable
(   Id int not null IDENTITY(1, 1) primary key
,   Make varchar(10)
,   Color varchar(25)
)

insert
    dbo.ExclusionTable
(   Make
,   Color
)
values
(   'Ford', null ),
(   'Audi', 'Red' ),
(   null, 'Grey')

select
    *
from
    dbo.DataTable dt
where
    not exists
    (   select
            *
        from
            dbo.ExclusionTable et
        where
            isnull(et.Make, dt.Make) = dt.Make
            and isnull(et.Color, dt.Color) = dt.Color
    )

This works because if the exclusion Make is null, we are comparing the data table's make to itself, which always gives true so we only are really comparing color.

The "not exists" is an efficient approach for large data sets, however you will want to define indexes if possible on your data table for sure, and possibly your exclusion table as well if it will contain many rows also.

I would propose the following two indexes on the data table:

create index ix_DataTable_1 on dbo.DataTable (Make, Color)
create index ix_DataTable_2 on dbo.DataTable (Color, Make)

Answer 3

You can use not exists:

select d.*
from data d
where not exists (select 1
                  from exclusions e
                  where (e.make = d.make or e.make is null) and
                        (e.color = d.color or e.color is null)
                 );