SQL server Where Clause variable may be null

Question

I have the following query (SQL server):

DECLARE @UserId INT;
SET @UserId = //... set by dynamic variable

SELECT *
FROM Users
WHERE userId = @UserId

My issue is that if the @UserId is null the query will not evaluate correctly.

How can I write this query to evaluate correctly if the variable is null or not null?

EDIT:

There have been many suggestions to use the following:

WHERE (@UserId IS NULL OR userId = @UserId)

OR similar.

In this case, if there is a table of 3 entries, with userId of 1,2 and 3 the variable '@UserId' IS NULL, this query will return all 3 entries. What I actually need it to return is no entries, as none of them have a userId of NULL

Possible duplicate of MySQL: selecting rows where a column is null — user3119231
– user3119231, Commented Jan 31, 2019 at 9:52

Thom A · Accepted Answer · 2019-01-31 10:24:15Z

3

You need to use an OR:

DECLARE @UserId INT;
SET @UserId = //... set by dynamic variable

SELECT *
FROM Users
WHERE (userId = @UserId OR @UserId IS NULL);

This, however, could well have (severe) performance issues if you're writing this in a Stored Procedure, reusing this code a lot or adding more NULLable parameters. If so, include OPTION (RECOMPILE) in your query so that the query plan is generated each time it's run. This will stop the Data Engine using query plans generated that had a different set of NULL parameters.

Edit: The OP wasn't clear on their question. They don't want to pass the value NULL for @UserID and return all rows, they want to pass NULL and get rows where UserID has a value of NULL. That would be:

SELECT *
FROM Users
WHERE UserID = @UserID
   OR (UserID IS NULL AND @UserID IS NULL);

edited Jan 31, 2019 at 10:24

answered Jan 31, 2019 at 9:51

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alex Over a year ago

am I correct in saying that if '@UserId' is null this will ignore this where clause altogether?

Thom A Over a year ago

It doesn't "ignore" the WHERE no @Alex , however, the part @UserId IS NULL will evaluate to TRUE if @UserID has a value of NULL. As either userId = @UserId OR @UserId IS NULL needs to evaluate to TRUE, all rows would be returned when @UserID has a value of NULL.

Alex Over a year ago

if there is a table of 3 entries, with userId of 1,2 and 3 the variable '@UserId' IS NULL, am I correct in saying that all 3 rows would be returned by this where clause?

Thom A Over a year ago

Why don't you test @Alex ? The syntax here is quite simple as well; is there a specific part you don't understand? (I've only added OR and IS NULL, so which of those are you having trouble with?)

Alex Over a year ago

My apologies, I should have done that. I have now run it and it is the case that this will bring back all records. I need to it only bring back records where userId is null when '@UserId' is null

|

PSK · Accepted Answer · 2019-01-31 12:19:20Z

2

After reading the edit, i think you want your query like

SELECT *
FROM Users
WHERE COALESCE(userId ,0) = COALESCE(@UserId,0)

Edit:

As pointed by Gordon Linoff & Larnu that above query will not be good in terms of performance as the query is "non-SARGable", for the better performance same query can be written as

  SELECT *
    FROM Users
    WHERE userId = @UserId OR( userId is null and @UserId is null)

edited Jan 31, 2019 at 12:19

answered Jan 31, 2019 at 9:49

PSK

18k5 gold badges35 silver badges44 bronze badges

6 Comments

ChrisCarroll Over a year ago

Use COALESCE ahead of ISNULL. Many reasons, main one in my view is that COALESCE is ANSI standard, ISNULL isn't. Some supporting discussions here: stackoverflow.com/questions/7408893/… or blogs.msdn.microsoft.com/sqltips/2008/06/26/…

PSK Over a year ago

Thanks @ChrisCarroll, I will go through the link, but i was in a impression that for a single condition match isnull is better.

Gordon Linoff Over a year ago

I don't support COALESCE() for this purpose. I makes assumptions about values in the data.

Thom A Over a year ago

It also makes the query non-SARGable; meaning performance will be poorer and only get worse as the table Users grows.

PSK Over a year ago

@GordonLinoff, thanks for letting me know this, i have updated the answer appropriately.

|

Zaynul Abadin Tuhin · Accepted Answer · 2019-01-31 09:49:21Z

1

use coalesce

SELECT *
FROM Users
WHERE userId = coalesce(@UserId,val)

answered Jan 31, 2019 at 9:49

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Comments

Yogesh Sharma · Accepted Answer · 2019-01-31 09:50:58Z

0

You can simplify the Boolean logic instead :

WHERE (@UserId IS NULL OR userId = @UserId)

answered Jan 31, 2019 at 9:50

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

Alex Over a year ago

am I correct in saying that if '@UserId' is null this will ignore this where clause altogether?

Anoos · Accepted Answer · 2019-01-31 09:59:19Z

0

Try this

DECLARE @UserId INT;
SET @UserId = //... set by dynamic variable

SELECT *
FROM Users
WHERE userId= (case when @UserId is null then userId else @UserId end)

answered Jan 31, 2019 at 9:59

Anoos

1867 bronze badges

Comments

Neil Alderson · Accepted Answer · 2022-07-11 15:03:19Z

0

I haven't seen the use of INTERSECT suggested so far so putting this out there as an alternative that also results in easy to read SQL when your list of potentially NULL variables might be long.

SELECT *
FROM Users
WHERE EXISTS (
   SELECT UserId
   INTERSECT SELECT @UserId
)

This pattern is useful when you have other variables to check e.g.

SELECT *
FROM Users
WHERE EXISTS (
   SELECT 
      UserId,
      Username,
      UserRole
   INTERSECT SELECT 
      @UserId,
      @Username,
      @UserRole
)

answered Jul 11, 2022 at 15:03

Neil Alderson

357 bronze badges

Comments

AmirHosseinMp02 · Accepted Answer · 2025-01-11 09:53:04Z

-1

Just set the ANSI_NULLS option to OFF and it will be all right(only usable for SQL Server 2017 (14.x) and older versions):

 SET ANSI_NULLS OFF;

Just to remind you, disabling this role is not a good option, but we can use it to maintain performance.

edited Jan 11 at 9:53

answered Jan 11 at 9:10

AmirHosseinMp02

773 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL server Where Clause variable may be null

7 Answers 7

6 Comments

6 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

6 Comments

6 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related