Why Doesn't SQL Support "= Null" Instead of "Is Null"

Why doesn't WHERE column = NULL throw an error in SQL Server?

That is because a NULL can't be equated to any value.

Disable the ANSI_NULLS option and then run it you will see the row now:

SET ANSI_NULLS OFF
select * from #foo --returns the one record we just created  
select * from #foo where colA = null --does not throw an error and does not return a record! why?? 
select * from #foo where colA is null --returns the record  drop table #foo 

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

How to use NULL or empty string in SQL

Select *
From Table
Where (col is null or col = '')

Or

Select *
From Table
Where IsNull(col, '') = ''

Difference between ColumnName = Null and ColumnName Is Null in SQL Server

Comparisons with null using normal operators are never true.

That's basically because null is "unknown" so it can't be determined whether it is or is not some value.

The special syntax is null and is not null must be used to determine nullness.

Why SQL does finally treat a NULL value as FALSE?

Within the context of a SELECT statement (so, in ON clauses, the WHERE clause, and within CASE expressions), predicates must be TRUE (not FALSE or UNKNOWN¹) for the predicate to be satisfied.

However, within CHECK constraints, predicates must not be FALSE in order to be satisfied.

I.e. the following script will work:

CREATE TABLE T (
  ID int not null,
  Val varchar(10) null,
  constraint CK_Vals CHECK (Val in ('abc','def'))
);
INSERT INTO T(ID,Val) VALUES (10,NULL);

So we can see that it is not universally true in SQL that UNKNOWN results are treated as FALSE. It's also trivially demonstrated by the fact that wrapping a predicate that produces UNKNOWN with NOT (<existing predicate>) does not produce TRUE.

The wikipedia page on Three-Valued logic covers a lot of details.

---

¹I'm assuming your question is about UNKNOWN rather than NULL, since you've tagged sql and relational-algebra. In standard SQL, UNKNOWN and NULL are two distinctly different concepts. Only (so far as I'm aware) mysql conflates the two.

SQL if not null update

If email OR password is not null update them otherwise let them be as they are.

You can use case expressions for this. I think that the logic you want is:

UPDATE users 
SET 
    username = Param1
    email = case when email is not null then Param2 end,
    password = case when password is not null then Param3 end
WHERE id = Param4;

Or if you want to update email and password if both are not null then:

UPDATE users 
SET 
    username = Param1
    email = case when email is not null and password is not null then Param2 end,
    password = case when email is not null and password is not null then Param3 end
WHERE id = Param4;

---

Now the question was updated and I understand that you want to perform the update if and only if both email and password parameters are not empty strings. So you actually want filtering. I would phrase this as:

UPDATE users 
SET username = Param1, email = Param2, password = Param3 
WHERE id = Param4 and Param2 <> '' and Param3 <> ''

Or if you want to separate the logic for both parameters:

UPDATE users 
SET 
    username = Param1, 
    email = case when Param2 <> '' then Param2 else email end, 
    password = case when Param3 <> '' then Param3 else password end
WHERE id = Param4;

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'

SQL: Select (null = null);

NULL stands for "Unknown Value". It is not known whether a value is true or false or anything else.

So, when comparing two unknown values, what would the answer be? Is UnknownA equal to UnknownB?

Answer? Unknown...

Here is an example for SQL Server:

IF (NULL = NULL)
  PRINT 'Equals'

IF (NULL != NULL)
  PRINT 'Not Equals'


IF (NULL IS NULL)
  PRINT 'IS'

IF (NULL IS NOT NULL)
  PRINT 'IS NOT'

The only thing that gets printed: IS

---

Related Topics