为什么column = NULL不返回任何行？ [重复]

Possible Duplicate:
Why does NULL = NULL evaluate to false in SQL server

可能重复:为什么NULL = NULL在SQL Server中评估为false

If you generate a query to insert the data in table "MyTab" for column --- Age, Sex, DOB, ID

如果您生成一个查询以在表“MyTab”中插入数据列--- Age,Sex,DOB,ID

INSERT INTO MyTab 
VALUES (22, '', '', 4)

What'll be the value in column Sex & DOB ? Is it NULL ?

Sex&DOB栏中的价值是多少?它是NULL吗?

If value is NULL then ---

如果值为NULL则---

 SELECT * FROM MyTab
 WHERE Sex=NULL

above query gives output ---- no rows selected --- why ??

上面的查询给出了输出----没有选择行---为什么?

if value is not NULL then ---

如果值不为NULL则---

 SELECT * FROM Mytab
 WHERE Sex IS NULL

above query gives the output ---- how ??

上面的查询给出了输出----如何?

5 个解决方案

#1

NULL is a special value in SQL denoting the absence of data. As such, you cannot do queries like:

NULL是SQL中的一个特殊值,表示缺少数据。因此,您无法执行以下查询:

SELECT fields FROM table WHERE column = NULL

NULL cannot be compared to anything, including NULL itself. Instead, you'd need:

NULL无法与任何内容进行比较,包括NULL本身。相反,你需要:

SELECT fields FROM table WHERE column IS NULL

However in your case you are really inserting an empty value in Sex and DOB.
And empty value is not NULL. You'd have to query for:

但是在你的情况下,你真的在Sex和DOB中插入一个空值。并且空值不是NULL。您必须查询:

SELECT fields FROM table WHERE column = ''

#2

From Null (SQL)

来自Null(SQL)

Misunderstanding of how Null works is the cause of a great number of errors in SQL code, both in ISO standard SQL statements and in the specific SQL dialects supported by real-world database management systems. These mistakes are usually the result of confusion between Null and either 0 (zero) or an empty string (a string value with a length of zero, represented in SQL as ''). Null is defined by the ISO SQL standard as different from both an empty string and the numerical value 0, however. While Null indicates the absence of any value, the empty string and numerical zero both represent actual values.

对Null如何工作的误解是SQL代码中大量错误的原因,无论是在ISO标准SQL语句中还是在现实世界数据库管理系统支持的特定SQL方言中。这些错误通常是Null与0(零)或空字符串(长度为零的字符串值,在SQL中表示为')之间混淆的结果。 Null由ISO SQL标准定义为与空字符串和数值0不同。虽然Null表示没有任何值,但空字符串和数字零都表示实际值。

Also from SET ANSI_NULLS

也来自SET ANSI_NULLS

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ value and that are not NULL.

当SET ANSI_NULLS为OFF时,Equals(=)和Not Equal To(<>)比较运算符不遵循SQL-92标准。使用WHERE column_name = NULL的SELECT语句返回column_name中具有空值的行。使用WHERE column_name <> NULL的SELECT语句返回列中包含非空值的行。此外,使用WHERE column_name <> XYZ_value的SELECT语句将返回非XYZ值且不为NULL的所有行。

#3

'' - it`s mean empty string. If you want to insert NULL,you need yo use

'' - 它的意思是空字符串。如果要插入NULL,则需要使用

 INSERT INTO MyTab 
          VALUES (22, '', NULL, 4)

#4

NULL means no data (not even blank or empty stuff), so Column = NULL is not going to work although Column IS NULL should return rows with that column as NULL. But you're inserting '' value so comparing with NULL won't return any rows.

NULL表示没有数据(甚至不是空白或空的东西),所以Column = NULL不起作用,尽管Column IS NULL应该返回该列为NULL的行。但是你要插入''值,所以与NULL比较不会返回任何行。

#5

As others said, first query returns no output because you cannot check for NULL with equals operator. The second query is more puzzling - my only guess is that you are using Oracle which treats empty strings as NULLs.

正如其他人所说,第一个查询不返回任何输出,因为你不能用equals运算符检查NULL。第二个查询更令人费解 - 我唯一的猜测是你使用的Oracle将空字符串视为NULL。

The answer to the question about values in Sex and DOB fields would depend on the type of those fields and database engine you are working with.

关于Sex和DOB字段中的值的问题的答案将取决于您正在使用的那些字段和数据库引擎的类型。

#1