T-SQL Variable in Where clause

Question

I want to return the records in this case that have company ABC... and contact is null. It will not return anything when @Contact is null. Or any variable for that matter. Ideas?

DECLARE @Customer NVARCHAR(40) = 'ABC Company',
        @Contact NVARCHAR(40) = NULL

SELECT
    Company
FROM
    company
WHERE
    contact = @Contact AND customer = @Customer

Thanks, EB

You can't get the `NULL` recode,when you use `=` – D-Shih Apr 05 '18 at 00:25 — D-Shih, Apr 05 '18 at 00:25

Joe Phillips · Answer 1 · 2018-04-05T00:31:16.407

5

NULL is special in that it means UNKNOWN.

A known value (contact) can never equal an unknown value. You need an OR statement to check if it's equal OR is null

where (contact = @Contact OR (contact is null AND @Contact is null))
and customer = @Customer

Maybe something like that?

edited Apr 05 '18 at 00:31

answered Apr 05 '18 at 00:25

Joe Phillips

49,743
32
103
159

Can you please expand a bit on that. Example of the OR statement? Thanks – EbertB Apr 05 '18 at 00:26
@EBaze The key is that NULL values cannot be compared to anything. If you want to equate 2 (or more) null values, you must use the "is null" expression for all of the compared values. You don't provide context but I'll guess that this is (or will be) a stored procedure you use for searching. If so, read Erland's discussion about the topic [here](http://www.sommarskog.se/dyn-search.html). – SMor Apr 05 '18 at 12:25

Sahi · Answer 2 · 2018-04-05T05:45:14.433

1

you can write

WHERE
ISNULL(contact,'') = ISNULL(@Contact,'') AND customer = @Customer

this do a null check and if null,then value will be considered as empty string for comparison.

instead of null==null (Which gives false), ''=='' will be performed.

    if(null =null)
        print 'Equal'

    else 
        print 'not equal'

/*******************************************/

    if('' ='')
        print 'Equal'

    else 
        print 'not equal'

edited Apr 05 '18 at 05:45

answered Apr 05 '18 at 04:20

Sahi

1,454
1
13
32

While this code may answer the question, providing additional context regarding **how** and/or **why** it solves the problem would improve the answer's long-term value. – Alexander Apr 05 '18 at 05:43
1

This part `ISNULL(contact,'')` recommends to read about [sargable predicates](https://stackoverflow.com/q/799584/5089204) – Shnugo Apr 05 '18 at 07:10

score 1 · Answer 3 · answered Apr 05 '18 at 07:29

In SQL, there is trivalent logic applied. In this reference you can read in detail about such logic. Bottom line is, that among true and false, there is another value: UNKNOWNN, which (in SQL) is the result of comparisons with NULL values. You can think of it as false (in this case).

Now, to visualise:

this queries won't return anything, as the where clause evaluates to UNKNOWN:

select 1 where null = 0
select 1 where null <> 0
select 1 where null = null

While this might be obvious, there's consequence: when you use not in operator. When right operand contains NULLs, the query will return no records, e.g.:

select 1 where 0 not in (null, 1, 2)

won't return anything. It's especially important when you put some query as right operand.

T-SQL Variable in Where clause

3 Answers3