Not Like Operator Weird Result Count

Question

I have a table with 31,483 records in it. I would like to search this table using both a LIKE and a NOT LIKE operator. The user can select the Contains or the Does Not Contain option.

When I do a LIKE the where clause is as follows

WHERE Model LIKE '%test%'

Which filters the result set down to 1345 records - so all is fine and Dandy

HOWEVER

I expected that running a not like on the table would result in n records where n = totalRowCount - LikeResultSet which results in an expected record count of 30138 when running a NOT LIKE operation.

I ran this WHERE clause:

WHERE Model NOT LIKE '%test%'

However it returned 30526 records.

I am assuming there is some intricacy of the NOT LIKE operator I am not realising.

so my question

Why arent I recieving a record count of TotalRows - LikeResults?

I am using SQL Server Compact 4.0 C# Visual Studio 2012

Answer 1

Check if some Model values are nulls, e.g. for the simple artifitial table

  with data as (
    select 'test' as model
     union all
    select 'abc'
     union all
    select 'def'
     union all
    select null -- <- That's vital
 )

you'll get

  -- 4 items
  select count(1)
    from data 

  -- 1 item: 'test'
  select count(1)
    from data 
   where model like '%test%'

  -- 2 items: 'abc' and 'def'
  select count(1)
    from data 
   where model not like '%test%'

And so 1 + 2 != 4