SQL Server Compare to NULL
I encountered the same problem with you when taking comparison with nullable value, NULL
always returns unknown
as far away of our desired only between TRUE
or FALSE
I ended up with declare a Scalar-valued functions with these logics like other SQL(s) dealing with null as
Ordinary comparison operators yield null (signifying "unknown"), not
true or false, when either input is null. For example, 7 = NULL yields
null, as does 7 <> NULL. When this behavior is not suitable, use the
IS [ NOT ] DISTINCT FROM constructs:
a IS DISTINCT FROM b => a != b
a IS NOT DISTINCT FROM b => a == b
Which a IS NOT DISTINCT FROM b
could be rewritten as
(a IS NOT NULL AND b IS NOT NULL AND a=b) OR (a IS NULL AND b is NULL)
I use sql_variant for these basic parameters: int, datetime, varchar,...
create function IsEqual(
@a sql_variant,
@b sql_variant
)
returns bit
as
begin
return (CASE WHEN (@a IS NOT NULL AND @b IS NOT NULL AND @a=@b) OR (@a IS NULL AND @b is NULL) THEN 1 ELSE 0 END);
end
create function IsNotEqual(
@a sql_variant,
@b sql_variant
)
returns bit
as
begin
return 1-dbo.IsEqual(@a,@b);
end
To use
select dbo.IsEqual(null, null) Null_IsEqual_Null,
dbo.IsEqual(null, 1) Null_IsEqual_1,
dbo.IsEqual(1, null) _1_IsEqual_Null,
dbo.IsEqual(1, 1) _1_IsEqual_1,
dbo.IsEqual(CAST('2017-08-25' AS datetime), null) Date_IsEqual_Null,
dbo.IsEqual(CAST('2017-08-25' AS datetime), CAST('2017-08-25' AS datetime)) Date_IsEqual_Date
For your cases
select dbo.IsNotEqual(123,123) _123_IsNotEqual_123,
dbo.IsNotEqual(5,123) _5_IsNotEqual_123,
dbo.IsNotEqual(Null,123) Null_IsNotEqual_123,
dbo.IsNotEqual(123,Null) _123_IsNotEqual_Null,
dbo.IsNotEqual(Null,Null) Null_IsNotEqual_Null
Comparing empty string with null value - SQL Server
Your first example returns fail because you have the wrong operator. If you want to see if something equals something else you use =
, not !=
Here is the code that proves that NULL
can be compared to ''
:
DECLARE @EmptyString VARCHAR(20) = '',
@Null VARCHAR(20) = Null;
SELECT
CASE WHEN ISNULL(@EmptyString, '') = ISNULL(@Null, '')
THEN 'Pass' ELSE 'Fail'
END AS EmptyStringVsNull
It returns pass because you use =
, not !=
Comparing a parameter against null values
This should work:
SELECT * FROM ApplicationData
WHERE (ApplicationId IS NULL AND @AppID IS NULL) OR ApplicationId = @AppID
This is an alternate approach:
SELECT * FROM ApplicationData
WHERE ISNULL(ApplicationId, -1) = ISNULL(@AppID, -1)
MySQL comparison with null value
In MySQL, NULL
is considered as a 'missing, unknown value', as opposed to no value. Take a look at this MySQL Reference on NULL.
Any arithmetic comparison with NULL
does not return true or false, but returns NULL
instead., So, NULL != 'C'
returns NULL
, as opposed to returning true.
Any arithmetic comparison with 'NULL' will return false. To check this in SQL:
SELECT IF(NULL=123,'true','false')
To check NULL
values we need to use IS NULL
& IS NOT NULL
operator.
How to compare values which may both be null in T-SQL
Use INTERSECT
operator.
It's NULL
-sensitive and efficient if you have a composite index on all your fields:
IF EXISTS
(
SELECT MY_FIELD1, MY_FIELD2, MY_FIELD3, MY_FIELD4, MY_FIELD5, MY_FIELD6
FROM MY_TABLE
INTERSECT
SELECT @IN_MY_FIELD1, @IN_MY_FIELD2, @IN_MY_FIELD3, @IN_MY_FIELD4, @IN_MY_FIELD5, @IN_MY_FIELD6
)
BEGIN
goto on_duplicate
END
Note that if you create a UNIQUE
index on your fields, your life will be much simpler.
SQL Server: Why does comparison null=value return true for NOT IN?
Common problem, canned answer:
The behavior of NOT IN clause may be confusing and as such it needs some explanations. Consider the following query:
SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)
Although there are more than a thousand distinct last names in AdventureWorks.Person.Contact, the query returns nothing. This may look counterintuitive to a beginner database programmer, but it actually makes perfect sense. The explanation consist of several simple steps. First of all, consider the following two queries, which are clearly equivalent:
SELECT LastName, FirstName FROM Person.Contact
WHERE LastName IN('Hedlund', 'Holloway', NULL)
SELECT LastName, FirstName FROM Person.Contact
WHERE LastName='Hedlund' OR LastName='Holloway' OR LastName=NULL
Note that both queries return expected results. Now, let us recall DeMorgan's theorem, which states that:
not (P and Q) = (not P) or (not Q)
not (P or Q) = (not P) and (not Q)
I am cutting and pasting from Wikipedia (http://en.wikipedia.org/wiki/De_Morgan_duality). Applying DeMorgan's theorem to this queries, it follows that these two queries are also equivalent:
SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)
SELECT LastName, FirstName FROM Person.Contact
WHERE LastName<>'Hedlund' AND LastName<>'Holloway' AND LastName<>NULL
This last LastName<>NULL can never be true
Comparisons with NULLs in SQL
Here is a nice comparison of null handling in SQLite, PostgreSQL, Oracle, Informix, DB2, MS-SQL, OCELOT, MySQL 3.23.41, MySQL 4.0.16, Firebird, SQL Anywhere, and Borland Interbase
Why does NULL = NULL evaluate to false in SQL server
Think of the null as "unknown" in that case (or "does not exist"). In either of those cases, you can't say that they are equal, because you don't know the value of either of them. So, null=null evaluates to not true (false or null, depending on your system), because you don't know the values to say that they ARE equal. This behavior is defined in the ANSI SQL-92 standard.
EDIT:
This depends on your ansi_nulls setting. if you have ANSI_NULLS off, this WILL evaluate to true. Run the following code for an example...
set ansi_nulls off
if null = null
print 'true'
else
print 'false'
set ansi_nulls ON
if null = null
print 'true'
else
print 'false'
Related Topics
Using Table Variable with Sp_Executesql
How to Perform a Select Query in a Do Block
Rails Scope to Check If Association Does Not Exist
SQL Server as Statement Aliased Column Within Where Statement
Is There a Performance Difference Between Between and in with MySQL or in SQL in General
Why Do I Need to Explicitly Specify All Columns in a SQL "Group By" Clause - Why Not "Group by *"
Group by Values That Are in Sequence
Recursive Stored Functions in MySQL
Changing SQL Server Database Sorting
How to Get Max(Date) from Given Set of Data Grouped by Some Fields Using Pyspark
Creating a Composite Foreign Key in SQL Server 2008
SQL Use Case Statement in Where in Clause
Find All Rows with Null Value(S) in Any Column