SQL 高手帮忙：一个简单问题

[旭日阳刚]

下图表示来自Access的某个数据库的一张table。
我写了一个很简单的SQL代码，想把first name为空的行过滤掉。
select *
from authors
where au_fname is not null;

但是得到是整张表。如果改成 is null, 却给我一张空表。我double check了table的名字authors和列的名字au_fname都是对的。
从图中可以看到这两个结果都不对。我试了这个数据库中别的table，都没有出现这个问题。有点让我抓狂。这究竟是怎么回事？

 

[旭日阳刚]

哇呀呀

 

[goal.web]

try IS NULL

select *
from authors
where au_fname IS NULL;

 

[旭日阳刚]

Thanks a lot.
But just tried and did not work. As I said, I ran the similar code on other tables from the same database, got desired results.

select *
from authors
where au_fname IS NULL;

 

[wiiwii]

is it au_fnam? you fool:flaming:

 

[uin]

those are not NULL, they may be ""

Thanks a lot.
But just tried and did not work. As I said, I ran the similar code on other tables from the same database, got desired results.

 

[旭日阳刚]

Great! I just changed the where clause to identify "", it worked!
But a natural question arises:
How to tell it is "" instead of NULL just by looking at the table?

Thank you very much, uin!

those are not NULL, they may be ""

 

[影子的声音]

try this:

select *
from authors
where trim(au_fname) is not null;

 

[旭日阳刚]

The picture just showed a semi-collapsed table. You foolish helper.

is it au_fnam? you fool:flaming:

 

[旭日阳刚]

Great! I just changed the where clause to identify "", it worked!
But a natural question arises:
How to tell it is "" instead of NULL just by looking at the table?

Thank you very much, uin!



引用:




作者: uin

those are not NULL, they may be ""

 

[闲得慌]

Great! I just changed the where clause to identify "", it worked!
But a natural question arises:
How to tell it is "" instead of NULL just by looking at the table?

Thank you very much, uin!

If you do this, your question is no longer important:


select *
from authors
where au_fname is not null and au_fname <> '';

 

[旭日阳刚]

我知道。那样确实提高了代码的可靠性。感谢。

但是我的问题可以帮助我进一步理解Access本身。
为什么看上去都是空白，有的可以用Null，而有的只能用""?

能简单指个方向吗？

If you do this, your question is no longer important:


select *
from authors
where au_fname is not null and au_fname <> '';

 

[闲得慌]

我知道。那样确实提高了代码的可靠性。感谢。

但是我的问题可以帮助我进一步理解Access本身。
为什么看上去都是空白，有的可以用Null，而有的只能用""?

能简单指个方向吗？

放进去的data不一样。

 

[四海漂萍]

查看au_fname关于default value的定义，如果default value定义的是NULL，你看到的空值就是NULL否则就是空（“”）。

我知道。那样确实提高了代码的可靠性。感谢。

但是我的问题可以帮助我进一步理解Access本身。
为什么看上去都是空白，有的可以用Null，而有的只能用""?

能简单指个方向吗？

 

[gottawa]

An Empty String Is Not Null. 数据库的NULL和""是有区别的