One, NULL does not support size/equality judgment
1, the following 2 queries, no matter how many records in the table users, the returned records are 0 rows
*
from
users
Select where
deleted_at =
null
;
*
from
users
Select where
deleted_at !=
null
;
用常规的比较操作符(normal conditional operators)来将 null 与其他值比较是没有意义的。 Null 也不等于 Null
2. The correct way to compare a value to NULL is to use the IS keyword, and the is not operator:
select
*
from
users
where
deleted_at
is
null
;
?
?二、not in 与 Null
1、not in 实例
子查询(subselect)是一种很方便的过滤数据的方法。例如,如果想要查询没有任何包的用户,可以编写下面这样一个查询:
select
*
from
users
where
id
not
in
(
select
user_id
from
packages)
2、假若 packages 表中某一行的 user_id 是 null 的话,返回结果是空的!
3、出现上述的原因
例如
select
*
from
users
where
id
not
in
(1, 2,
null
)
这个SQL语句会等效于
select
*
from
users
where
id != 1
and
id != 2
and
id !=
null
4、in查询
select
*
from
users
where
id
in
(1, 2,
null
)
等效于
select
*
from
users
where
id = 1
or
id = 2
or
id =
null
三、GROUP BY会把NULL分到一个组
? ?
four, null and sortWhen sorting, null values are considered to be the largest. When sorting in descending order (descending), the null value is at the top of the line.
Reference: Places to be aware of when using Null values in SQL http://www.studyofnet.com/news/1037.html
What you need to be aware of when using Null values in SQL