How to handle null values in SQL

Time:2021-7-24

Do you care about null value when using database everyday?

In fact, null value is a very special and interesting existence in the database. Let’s take a look at it together;

Do you want to accurately find the MySQL articles you want to read? Here →   MySQL column directory | click here

When querying the database, if you want to know a column (for example, user registration age, user)_ Age) is null. How to write the SQL query statement?

Here’s the thing:


SELECT * FROM TABLE WHERE USER_AGE = NULL

Still?


SELECT * FROM TABLE WHERE USER_AGE IS NULL

Of course, the correct way of writing should be the second(WHERE USER_AGE IS NULL)。

But why? We don’t use the “is” keyword when comparing database data, do we?

 

puzzled face

For example, if we want to know whether the value of a column is equal to 1, the where statement is as follows:


WHERE USER_AGE = 1

Then why use is keyword for null value? Why deal with null in this way?

Because,In SQL, null means “unknown”. That is, a null value represents an “unknown” value.

Null = unknown;

In most databases, null is different from an empty string.

But not all databases are like this. For example, Oracle does not support empty strings. It will automatically convert empty strings to null values.

In most other databases, null values and strings are handled differently:

  • Although the empty character (“”) string indicates “no value”, this value is known.
  • Null means “unknown value”, which is unknown.

It’s like I asked a question: “what’s the nickname of chuanjianguo?”

Someone will answer, “I don’t know what Chuan Jianguo’s nickname is.”. In this case, it can be used in the databaseNicknameColumn to represent the small name of chuanjianguo, and the value of this column is null.

Some people will answer, “chuanjianguo has no nickname. His parents didn’t give him a nickname. Although people have always called him Chuaner dog, I know chuanjianguo really doesn’t have a nickname “. In this case,NicknameColumn should be an empty string (”).

Oracle is special. Both values are represented by null, while most other databases will treat them differently.

However, as long as you remember that null represents an unknown value, it will be handy when writing SQL query statements.

For example, if you have a query like this:


SELECT * FROM SOME_TABLE WHERE 1 = 1

This query will return all rows (assuming home)_ Table is not an empty table) because the expression ‘1 = 1’ must be true.

If I write this:


SELECT * FROM SOME_TABLE WHERE 1 = 0

The expression “1 = 0” is false. This query statement will not return any data.

But if I write this:


SELECT * FROM SOME_TABLE WHERE 1 = NULL

At this time, the database does not know whether the two values (1 and null) are equal, so it will be determined as “null” or “unknown”, so it will not return any data.

Ternary logic

Where in SQL query statements generally has three results:

  • It can be true (data will be returned at this time);
  • It can be false (no data will be returned at this time);
  • It can also be null or unknown (data will not be returned at this time);

You might think, “in that case, why should I care whether it is false or null? Don’t they all return data? “

Next, let me tell you where there are problems: let’s take a lookNOT( ) method.

Suppose there is such a query statement:


SELECT * FROM SOME_TABLE WHERE NOT(1 = 1)

The database will first calculate 1 = 1, which is obviously true.

Next, the database will apply the not () condition, so where returns false.

Therefore, the above query will not return any data.

But if you change the statement to this:


SELECT * FROM SOME_TABLE WHERE NOT(1 = 0)

The database will first calculate 1 = 0, which must be false.

Then, the database applies the not () condition, and the opposite result becomes true.

Therefore, this statement will return data.

But what if you change the sentence to the following?


SELECT * FROM SOME_TABLE WHERE NOT(1 = NULL)

The database first calculates 1 = null. It does not know whether 1 is equal to null because it does not know what the value of null is.

Therefore, this calculation will not return true or false, it will return a null.

Next, not () will continue to parse the result returned by the previous calculation.

When not() encounters null, it generates another null. The opposite of the unknown is another unknown.

Therefore, for these two queries:


SELECT * FROM SOME_TABLE WHERE NOT(1 = NULL)
SELECT * FROM SOME_TABLE WHERE 1 = NULL

Will not return data, although they are the exact opposite.

Null and not in
If I have such a query statement:


SELECT * FROM TABLE WHERE 1 IN (1, 2, 3, 4, NULL)

Obviously, where returns true, and this statement will return data, because 1 exists in the bracketed list.

But if you write this:


SELECT * FROM SOME_TABLE WHERE 1 NOT IN (1, 2, 3, 4, NULL)

Obviously, where returns false, and the query will not return data, because 1 exists in the bracketed list, but we say “not in”.

But what if we change the sentence to this?


SELECT * FROM SOME_TABLE WHERE 5 NOT IN (1, 2, 3, 4, NULL)

Where here does not return data because its result is not true. The number 5 may or may not exist in the bracketed list because there is a null value (the database does not know what the null value is).

This where will return null, so the entire query will not return any data.

I hope everyone knows how to handle null values in SQL statements now. For more information about SQL processing null values, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!