How can you use NULLIF in the where clause?

Question

I am trying to do something like this:

select
     col_1, col_2, etc
from
     table
where
     col_1 = nullif('', '')

Am I doing this incorrectly? I am not getting any results back.

Edit:

My expected results are to get every record back where col_1 is NULL.

I know I can use where col_1 is null, but I am using SSIS and a variable. Sometimes the col_1 is actually NULL and sometimes it is not.

Sample data:

 collaboration     first_name     last_name          city     
          NULL            Bob         Smith       Chicago
Data Migration           John         Smith        Austin
          NULL           Pika           Chu       Houston
    Production            ash       ketchum         tokyo

Sometimes I may want to return the records where collaboration is NULL, sometimes I want to return the records where it says Production.

I'd like to use the same query, if possible, with little modification.

Edit Part 2:

I tried to experiment with this.

select
     col_1, col_2, etc
from
     table
where
     case
         when col_1  = '' then NULL
         else col_1
         end

But I am getting the error message:

An expression of non-boolean type specified in a context where a condition is expected, near ORDER.

Query speed it not something I am concerned with.

What are you trying to do? Did you look at the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql?view=sql-server-2017) on how to use NULLIF()? — HoneyBadger, Mar 19 '19 at 14:46
`col_1 = nullif('', '')` would be the same as `Col_1 = NULL`, which is never going to return true; as nothing equals `NULL` (including `NULL`). — Thom A, Mar 19 '19 at 14:47
Documentation : `Returns a null value if the two specified expressions are equal.` — MatBailie, Mar 19 '19 at 14:48
You state that know you can use `where col_1 is null`, but I don't understand why you aren't. Please show the actual case in SSIS where the existence of a variable prevents you doing that. — MatBailie, Mar 19 '19 at 14:49
If you can give us some sample data and expected results, it would be a lot easier. — DxTx, Mar 19 '19 at 14:51
I edited my post to do so, let me know if you have questions. — Chicken Sandwich No Pickles, Mar 19 '19 at 14:56
Are you looking for a parametrised query then? This is still unclear — Thom A, Mar 19 '19 at 14:58
It sounds like you're trying to write `WHERE col1 = @variable OR (col1 IS NULL AND @variable = '')` or something similar. That has a performance penalty though. It's better to have your SSIS package write two different queries. One query for when you want the NULLs, and a separate query for when you want to match the variable. That way each query can have its own execution plan, making active use of appropriate indexes, rather than trying to form one complex execution plan to fulfil two requirements, compromising it's ability to be optimal for either circumstance. — MatBailie, Mar 19 '19 at 14:59
@MatBailie I'm not sure that would so much. The issue stems more when you have a "catch-all query" like `WHERE Column = @Var OR @Var IS NULL`, however, effectively the OP appears to want to have `NULL = NULL` logic; which won't have the same concerns. — Thom A, Mar 19 '19 at 15:02
@larnu In my experience, even a `UNION ALL` of the two simpler queries will be faster, even in this simpler case. — MatBailie, Mar 19 '19 at 15:15

DDS · Accepted Answer · 2019-03-19T15:23:53.450

3

This is the query you need

select
     col_1, col_2, etc
from
     table
where
     col_1 is null

is null checks if a column is null, nullif(@expr1,@expr2) could be rewritten as:

case when @expr1 = @expr2 return null else return @expr1 end

EDIT: you can relax filters adding OR condition into the 'where' clause (TIP: remember AND is evaluated before OR)

select
     col_1, col_2, etc
from
     table
where
     (col_1 is null OR col1 like 'production')

if you want to decide runtime wich one you neeed you could write a procedure:

create proc my_proc @var AS varchar(100) = 'NULL§159§' -- this defaults to null, if you put a parameter it queries with parameter passed
as
select
         col_1, col_2, etc
    from
         table
    where
         WHERE coalesce(col_1,'NULL§159§') = @var 
-- added §159§ symbol to the null to make sure the queried string is impossible in the database, 
-- obviously into the database the value 'NULL159' hase become a sort of 'reserved word', but hopefully is odd enough not to appear in data
GO

and call it by exec my_proc('production')

edited Mar 19 '19 at 15:23

answered Mar 19 '19 at 14:49

DDS

2,340
16
34

Sometimes it is null, sometimes it is not. I need to be able to handle both cases. – Chicken Sandwich No Pickles Mar 19 '19 at 14:49
2

Your question states *"My expected results are to get every record back where col_1 is NULL."* @LunchBox ; this is **exactly** what this answer does. – Thom A Mar 19 '19 at 14:52
1

put sample data and desired output to let us understand what you really need because according to what you're currently asking this is the answer – DDS Mar 19 '19 at 14:52
@larnu This bit of the OP's edit contradicts the original content `I know I can use where col_1 is null, but I am using SSIS and a variable. Sometimes the col_1 is actually NULL and sometimes it is not.` *(Which makes no sense to me)* Until the OP elaborates on that, everything else is supposition and guess work. – MatBailie Mar 19 '19 at 14:54
Let me try that DDS, one sec – Chicken Sandwich No Pickles Mar 19 '19 at 14:57
1

The use of an SP to encapsulate the behaviour is a decent one. But, as written, the default argument will always cause the SQL to return no records. *(I'd use an `IF` to have a second query with `WHERE col_1 IS NULL`, others may have just a single query with `WHERE col_1 = @var OR (col_1 IS NULL and @var IS NULL)`)* – MatBailie Mar 19 '19 at 15:14
I edited my post using an idea from your solution. I am getting errors, but I feel we are closer. – Chicken Sandwich No Pickles Mar 19 '19 at 15:17
@dds - That's a dangerous approach. It assumes *(possibly correctly, but even then it may fail in the future)* that col1 never includes the value `'NULL'`. If it ever does include that value, you'll get false positive matches. – MatBailie Mar 19 '19 at 15:23
True, added some randomness to the string (and also an explanation of what is done) – DDS Mar 19 '19 at 15:24
You don't need the randomness, just use `WHERE col_1 = @var OR (col_1 IS NULL and @var IS NULL)` – MatBailie Mar 19 '19 at 15:29
Correct, but it's,in my opinion, too verbose and harder to understand. – DDS Mar 19 '19 at 15:30
1

Correctness, robustness, maintainability, and performance are all compromised by your choice. If 25 years of coding taught me anything, it's that hacks for conciseness are still hacks and they always come back and bite you, one way or another. But it's your answer. – MatBailie Mar 19 '19 at 15:36

score 2 · Answer 2 · answered Mar 19 '19 at 14:54

2

Try this, it can handle the column with null values or empty space

SELECT
     col_1, col_2, etc
FROM
     Table
WHERE
     ISNULL(NULLIF(col_1 ,''),'1') = '1'

answered Mar 19 '19 at 14:54

Sreenu131

2,476
1
7
18

2

Boolean logic is the better way to handle things in the `WHERE`. This could have some (severe) performance issues as `NULLIF` and `ISNULL` will make the query non-SARGable. – Thom A Mar 19 '19 at 14:56
Interesting. If I replace the '1' with 'THIS WAS A NULL VALUE', it does not work, but it does with '1'. – Chicken Sandwich No Pickles Mar 19 '19 at 15:52
It doesn't need to say 1 or the other, I was just curious. – Chicken Sandwich No Pickles Mar 19 '19 at 15:53

score 0 · Answer 3 · answered Mar 19 '19 at 14:52

0

You can do something like

select
     col_1, col_2, etc
from
     table
where
     col_1 IS NULL OR col_1 = ''

answered Mar 19 '19 at 14:52

M.Ali

67,945
13
101
127

I want 1 or the other, but not both. – Chicken Sandwich No Pickles Mar 19 '19 at 14:57

score 0 · Answer 4 · answered Mar 19 '19 at 14:57

0

select
     col_1, col_2, etc
from
     table
where
     collaboration IS NULL OR collaboration ='Production'

answered Mar 19 '19 at 14:57

Hasan Mahmood

978
7
10

Please add a little information to explain how this solves the problem. Thanks! – Jonathan Mar 19 '19 at 17:14

score 0 · Answer 5 · answered Mar 19 '19 at 15:00

0

Crystal ball time from me. This is my guess on what the OP wants:

DECLARE @Prod varchar(15);
--SET @Prod = 'Production';

SELECT {Columns}
FROM YourTable
WHERE Col1 = @Prod
   OR (Col1 IS NULL AND @Prod IS NULL);

answered Mar 19 '19 at 15:00

Thom A

88,727
11
45
75

score 0 · Answer 6 · answered Mar 19 '19 at 15:26

0

Try this.

DECLARE @SearchValue VARCHAR(50)
SELECT col_1, col_2, etc
FROM YourTable
WHERE ISNULL(col_1,'') = ISNULL(@SearchValue,'')

answered Mar 19 '19 at 15:26

Musheer Khalid

3
3

How can you use NULLIF in the where clause?

6 Answers6