Is Null VS = Null in Where Clause + SQL Server

IS NULL vs = NULL in where clause + SQL Server

select column1 from Table1
  where (@param is null and column2 is null)
     or (column2 = @param)

SQL is null and = null

In SQL, a comparison between a null value and any other value (including another null) using a comparison operator (eg =, !=, <, etc) will result in a null, which is considered as false for the purposes of a where clause (strictly speaking, it's "not true", rather than "false", but the effect is the same).

The reasoning is that a null means "unknown", so the result of any comparison to a null is also "unknown". So you'll get no hit on rows by coding where my_column = null.

SQL provides the special syntax for testing if a column is null, via is null and is not null, which is a special condition to test for a null (or not a null).

Here's some SQL showing a variety of conditions and and their effect as per above.

create table t (x int, y int);
insert into t values (null, null), (null, 1), (1, 1);

select 'x = null' as test , x, y from t where x = null
union all
select 'x != null', x, y from t where x != null
union all
select 'not (x = null)', x, y from t where not (x = null)
union all
select 'x = y', x, y from t where x = y
union all
select 'not (x = y)', x, y from t where not (x = y);

returns only 1 row (as expected):

TEST    X   Y
x = y   1   1

See this running on SQLFiddle

SQL Server : check if variable is Empty or NULL for WHERE clause

Just use

If @searchType is null means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR @SearchType is NULL

If @searchType is an empty string means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR @SearchType = ''

If @searchType is null or an empty string means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR Coalesce(@SearchType,'') = ''

SQL server Where Clause variable may be null

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)

IN Clause with NULL or IS NULL

An in statement will be parsed identically to field=val1 or field=val2 or field=val3. Putting a null in there will boil down to field=null which won't work.

(Comment by Marc B)

I would do this for clairity

SELECT *
FROM tbl_name
WHERE 
(id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL)

WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value

Here is how you can solve this using a single WHERE clause:

WHERE (@myParm = value1 AND MyColumn IS NULL)
OR  (@myParm = value2 AND MyColumn IS NOT NULL)
OR  (@myParm = value3)

A naïve usage of the CASE statement does not work, by this I mean the following:

SELECT Field1, Field2 FROM MyTable
WHERE CASE @myParam
    WHEN value1 THEN MyColumn IS NULL
    WHEN value2 THEN MyColumn IS NOT NULL
    WHEN value3 THEN TRUE
END

It is possible to solve this using a case statement, see onedaywhen's answer

LIKE and NULL in WHERE clause in SQL

You can use condition like this in you where clause

where @Keyword is null or CustomerName like '%' + @Keyword + '%' 

NULL value in where clause

By default, in SQL Server, comparison operators return UNKNOWN (i.e. not true or false) if either value is a null.

MSDN IS [NOT] NULL documentation

There are ways to change how SQL Server handles null comparisons with things like SET ANSI_NULLS. Definitely pay attention to upcoming changes to the default for this value.

Here is an interesting article which covers several issues related to null handling.

Why rows with NULL in column used in WHERE clause are omitted in results?

Is that bug in SQL server or I don't know something

Well, it's not a bug.

Think of NULL as a placeholder for "Unknown" and it will be clearer.

If I ask you to find me all the rows where the value is not 2 then you cannot return any NULL (unknown) value since you do not know that it is NOT 2.

If you want to include NULLs then the criteria should be

where value != 2 or value is null;

---

Related Topics