Null Values in SQL Queries

栏目: IT技术 · 发布时间: 4年前

内容简介:Today’s post is about NULL values in SQL, and comes courtesy of my friend and database wizard, Kaley. You should check out hisHere’s a topic that gets a lot ofbudding developers in trouble–the concept of NULL values in SQL queries.Whenever you issue a SQL

Today’s post is about NULL values in SQL, and comes courtesy of my friend and database wizard, Kaley. You should check out his website if you’d like to learn more about SQL, Oracle database, and making queries run faster .

Here’s a topic that gets a lot ofbudding developers in trouble–the concept of NULL values in SQL queries.

Whenever you issue a SQL query to a database…and you want to know whether a column has a NULL value in it…what is the proper way to write a query that will find the result?

Should you use a query like this?

SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN = NULL

Or! Should you use a query like this?

SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN IS NULL

…The answer is, you should be using the second query (WHERE SOME_COLUMN IS NULL).

Now why is that ?

We don’t use the “ IS ” keyword with any othercomparisons in the database, right?

If we want to know if a field is equal to one, we use a WHERE clause like this:

WHERE SOME_COLUMN = 1

So why on earth would we do the IS keyword with a NULL value? Why would we need to treat NULL differently?

The answer is this: In SQL, NULL represents the concept of “unknown” (so a NULL value represents an “unknown” value).

Null as an Unknown

In most databases, there is a difference between NULL and an empty string (represented by a “double apostrophe” or ”).

But this isn’t always true for all databases:  For example, Oracle database won’t allow you to have an empty string. Anytime Oracle database sees an empty string, it automatically converts the empty string into a NULL value.

For the majority of the other databases out there, though, a NULL value is treated differently than an empty string:

  • An empty string is treated like a known value where there is no value.
  • A NULL value is treated like a value that is not known .

This would be the difference between me asking the question, “What was U.S President Theodore Roosevelt’s middle name?”

  • One answer might be, “Well, I don’t know what Theodore Roosevelt’s middle name is.” (This idea could be represented by a NULL value in the MIDDLE_NAME column for Theodore Roosevelt’s record)
  • Another possible answer could be “President Theodore Roosevelt actually didn’t have a middle name. His parents never gave him a middle name, and I know for a fact that Theodore Roosevelt didn’t have a middle name.” (You would represent that by putting an empty string, or a ” into the MIDDLE_NAME column)

Oracle database is the most notable exception where those two values are actually both going to be represented by NULL–most databases other than Oracle are going to treat NULL and an empty string very differently.

As long as you can remember that a NULL value represents an unknown value, then this is going to help you craft your SQL queries, and help you work around some of the trickier situations that you can get in with NULL values.

If, for example, you were to have a query that uses a WHERE clause like this:

SELECT * FROM SOME_TABLE
WHERE 1 = 1

This query will return rows (assuming SOME_TABLE isn’t an empty table!) because the expression “1 = 1” is provably true…it can be proven to be true.

If I were to say:

SELECT * FROM SOME_TABLE
WHERE 1 = 0

…then the database sees this and evaluates “1 = 0” as false (meaning this query would never return any rows).

But if I were to say:

SELECT * FROM SOME_TABLE
WHERE 1 = NULL

The database basically goes, “I don’t know if these two values (1 and our black-box NULL value) are equal”…so it doesn’t return any records.

Ternary Logic

When you have a WHERE clause in a SQL query, it can have one of three different results:

  • It can be true (and it will return rows)
  • It can be false (and it won’t return rows)
  • Or it can be NULL or unknown (an unknown is also not going to return values)

You may be thinking, “Okay, but why do I care that there’s a difference between false and null since the database handles those two values exactly the same?”

Well, let me show you where you can run into trouble:  Let’s introduce the NOT() condition.

If you were to say:

SELECT * FROM SOME_TABLE
WHERE NOT(1 = 1)

Then the database is first going to evaluate 1 = 1 and say, “Okay, that’s clearly true.”

But then it’s going to apply the NOT() condition to it. The database is going to go, “Well true, when notted, turns to false…so the NOT() condition causes our WHERE clause to be false here.”

So the query above isn’t going to return any records.

However, if you were to say:

SELECT * FROM SOME_TABLE
WHERE NOT(1 = 0)

Then the database first evaluates expression 1 = 0 and says, “That’s clearly false.”

But then it’s going to apply the NOT() condition, which will give us the opposite result, so it becomes true .

So this query will return records!

What if I issued the following query though?

SELECT * FROM SOME_TABLE
WHERE NOT(1 = NULL)

The database is first going to evaluate 1 = NULL. (Remember, it’s going to treat NULL like an unknown value!)

It’s going to say, “I can’t say whether or not 1 is equal to NULL because I don’t know what the NULL (unknown) value is.”

So it doesn’t yield a true result , and it doesn’t yield a false result –it instead yields a NULL (or unknown) result.

This NULL result is going to be interpreted by the NOT() operator.

Whenever you take a NULL and you put it in a NOT() condition…the result is another NULL! (the opposite of unknown is…well…another unknown).

So the NOT() operator doesn’t do anything with null conditions.

So NEITHER of these queries…

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

…is ever going to return any records…even though they’re opposites!

NULL and NOT IN

If I issued a query with a WHERE clause like this:

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

…then clearly the WHERE clause is going to be true, and this query will return records, since 1 is in our IN list…

But if I were to say:

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

Then clearly this is going to be false, and this query will never return records, since the number 1 appears in our IN list and we’re saying “NOT IN”…

Now what if I were to say something like this?

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

This WHERE clause will never return any records, since it is not provably true (it cannot be proven to be true). The number 5 doesn’t explicitly appear in the “IN” list–But 5 could be inside our “black box” NULL value (the database doesn’t necessarily know what the value of NULL is).

This yields a NULL result (meaning an unknown result) and this WHERE clause is never going to return any records.

This is why it’s important to consider a NULL value to be equivalent to an unknown value–it’s going to help you whenever you craft complex SQL queries.

Hopefully you’re now equipped to deal with NULL values in SQL queries! For more information on SQL, Oracle database, and making queries run faster, visit blog.tuningsql.com .


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

软件的奥秘

软件的奥秘

[美] V. Anton Spraul / 解福祥 / 人们邮电出版社 / 2017-9-1 / 49

软件已经成为人们日常生活与工作中常见的辅助工具,但是对于软件的工作原理,很多人却不是非常了解。 本书对软件的工作原理进行了解析,让读者对常用软件的工作原理有一个大致的了解。内容涉及数据如何加密、密码如何使用和保护、如何创建计算机图像、如何压缩和存储视频、如何搜索数据、程序如何解决同样的问题而不会引发冲突以及如何找出最佳路径等方面。 本书适合从事软件开发工作的专业技术人员,以及对软件工作......一起来看看 《软件的奥秘》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

SHA 加密
SHA 加密

SHA 加密工具

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具