-2

I am working with one query in MSSQL like this:

select company_id,isshowable from company where company_id in (1,2);

It will give results like:

 company_id | isshowable    
      1     |    NULL  
      2     |    true

Here, company_id 1 has isshowable = NULL value. When I apply the isnull function with this query:

select isshowable, company_id
from company
where company_id in (1,2)
    and (isshowable='true' OR isshowable = ISNULL(isshowable,'true'));

It gives only one record which has company_id = 2. Why is the ISNULL function is not replacing NULL value with 'true'?

My requirement is that both records should be fetched from the database, and NULL values should be replaced with 'true'.

What should be a proper way to achive this ?

beercohol
  • 2,577
  • 13
  • 26
Mitul Chauhan
  • 49
  • 1
  • 2
  • 9

1 Answers1

1

Because NULL <> 'true'.

Your WHERE clause:

isshowable = ISNULL(isshowable, 'true'))

returns false if isshowable is NULL. Remember that ISNULL function returns the 2nd argument if the first argument is NULL. So in this case, your ISNULL returns true. But the left hand side of the equation is still NULL, so it became:

`NULL` = 'true'

which, as you know, is false, for the purpose of the WHERE clause. (Note that SQL Server uses three-valued logic, so NULL = false is really = UNKNOWN)

To achieve your desired outcome, your WHERE clause should be:

WHERE
    company_id in (1,2) 
    AND (isshowable = 'true' OR isshowable IS NULL)
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67