how to access UNKNOWN in sql server's boolean?

Question

Hi I learned sql server's BIT has true, false, and unknown. For example, the comparison 1 > NULL yields unknown.

I know I can check it indirectly: if x is null or y is null, then the comparison x > y must be unknown.

Is there a way to access unknown directly? For example

select *
into #t
from (
    SELECT 1 as [x], 1 as [y] UNION ALL
    SELECT 1 as [x], 2 as [y] UNION ALL
    SELECT 1 as [x], NULL as [y]
) as a


SELECT *
from #t
--x y
--1 1
--1 2
--1 NULL

select *
    ,/* ???? */ as [is x > y]
from #t
--want to have:
--x y      is x > y
--1 1      0
--1 2      0
--1 NULL   unknown

I think "unknown" is represented as `NULL`. – Gordon Linoff May 26 '17 at 22:07 — Gordon Linoff, May 26 '17 at 22:07

score 1 · Answer 1 · answered May 26 '17 at 22:19

1

This will work:

select *
into #t
from (
    SELECT 1 as [x], 1 as [y] UNION ALL
    SELECT 1 as [x], 2 as [y] UNION ALL
    SELECT 1 as [x], NULL as [y]
) as a


SELECT *
from #t
--x y
--1 1
--1 2
--1 NULL

select *,
    case  
        when x > y then '1' 
        when x is null or y is null then 'unknown'
        else '0' 
        end as [is x > y]
from #t

--x y    is x > y
--1 1    0
--1 2    0
--1 NULL unknown

-- Don't forget to delete your temp table when done. 
drop table  #t

answered May 26 '17 at 22:19

Chuck

1,001
1
13
19

Thank u @Chuck isn't the idea of temp table that u don't need to drop it? – YJZ May 27 '17 at 15:13
Hi Y Zhang, I guess that it is just a best practice, see here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/522d302a-857f-4730-b49f-cca7fb236912/is-it-necessary-to-clean-up-drop-temporary-tables-in-stored-procedures?forum=transactsql – Chuck May 30 '17 at 19:39

score 0 · Answer 2 · answered May 26 '17 at 22:19

You'll need a CASE expression with an IS NULL predicate to return the string 'unknown' as the last column value, which will also require the other '0' and '1' values as varchar literals to avoid implicit conversion of the 'unknown' string literal to int.

SELECT
      x
    , y
    , CASE WHEN x > y THEN '1' WHEN x <= y THEN '0' ELSE 'unknown' END AS [is x > y]
FROM #t;

how to access UNKNOWN in sql server's boolean?

2 Answers2