CODEWITHSUNDEEP
sqloracle-databasenullsql-like
HuyR
HuyR

Reputation: 15

null LIKE '%text%'?

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?

Upvotes: 1

Views: 155

Answers (4)

ramana_k
ramana_k

Reputation: 1933

In the first case, left join is returning all rows and then the where clause is filtering out rows due to Nulls

In the second case the where clause is filtering out rows due to nulls. Then the left join is adding them back in. If you run EXPLAIN PLAN,you can see the order of operations, whether the filtering due to null is happening first or the inclusion due to left join is happening

Upvotes: 0

Adithya Rao
Adithya Rao

Reputation: 165

When you are evaluating NULL AND or OR Condition the result is always false.

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%'

Here left join on client results in cl.name_full as NULLS for cases where record is not present in credit_case.

Try this you will understand : 

select cl.name_full
from credit_case cc
left join (select skp_case, name_full from client) cl on cc.skp_case = cl.skp_case

All NULL records are the ones that are omitted from the query result.

On the other hand, for the second case, when you re-write 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

Here assuming name_full is not NULL, all records not having '%TEST%' in name_full is shown. And NULL is not evaluated with '%TEST%' here.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231741

  • 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.

Upvotes: 3

ydoow
ydoow

Reputation: 3006

On the first case, there's a condition clause that will filter out some of records from the result.

On the second case, there's no condition clause on the master table. What you're doing is actually

select * 
from credit_case cc
left join [SUBTABLE]

so that would definitely give you a complete list of the master table, credit_case

Upvotes: 0

Related Questions