is null vs. equals null

Question

I have a rough understanding of why = null in SQL and is null are not the same, from questions like this one.

But then, why is

update table 
set column = null

a valid SQL statement (at least in Oracle)?

From that answer, I know that null can be seen as somewhat "UNKNOWN" and therefore and sql-statement with where column = null "should" return all rows, because the value of column is no longer an an unknown value. I set it to null explicitly ;)

Where am I wrong/ do not understand?

So, if my question is maybe unclear: Why is = null valid in the set clause, but not in the where clause of an SQL statement?

Answer 1

They completely different operators, even if you write them the same way.

• In a where clause, is a comparsion operator
• In a set, is an assignment operator

The assigment operator allosw to "clear" the data in the column and set it to the "null value" .

Answer 2

SQL doesn't have different graphical signs for assignment and equality operators like languages such as c or java have. In such languages, = is the assignment operator, while == is the equality operator. In SQL, = is used for both cases, and interpreted contextually.

In the where clause, = acts as the equality operator (similar to == in C). I.e., it checks if both operands are equal, and returns true if they are. As you mentioned, null is not a value - it's the lack of a value. Therefore, it cannot be equal to any other value.

In the set clause, = acts as the assignment operator (similar to = in C). I.e., it sets the left operand (a column name) with the value of the right operand. This is a perfectly legal statement - you are declaring that you do not know the value of a certain column.

Answer 3

In the set clause, you're assigning the value to an unknown, as defined by NULL. In the where clause, you're querying for an unknown. When you don't know what an unknown is, you can't expect any results for it.