null LIKE '%text%'?

Question

I'm missing many records due to the condition not like '%TEST%' althought that field contains a NULL value.

select * 
from credit_case cc
left join (select skp_case, name_full from client) cl on cc.skp_case = cl.skp_case
where cl.name_full not like '%TEST%'

Table credit_case contains full data whereas table client does not.

When I re-write it as

select * 
from credit_case cc
left join (select skp_case, name_full from client 
           where name_full not like '%TEST%') cl on cc.skp_case = cl.skp_case

records from credit_case are not lost. Why is it?

Answer 1

• null is never equal to another value, including null.
• null is never unequal to another value, including null.
• null is never like another value, including null.
• null is never unlike another value, including null.

The only way to do comparisons with null is to use is null or is not null. None of these queries will ever return a row.

select *
  from table
 where column = null;

select *
  from table
 where column != null;

select *
  from table
 where column like null;

select *
  from table
 where column not like null;

You would need to explicitly include an is null or an is not null clause

where (   cl.name_full not like '%TEST%'
       or cl.name_full is null)

will return the rows with null values for name_full.