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

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 2

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" .