What you need to be aware of when using Null values in SQL

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 wheredeleted_at = null;

* from users Select wheredeleted_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