SQL反模式14-对未知的恐惧

    你的account表中有first_name, last_name两个字段，现在有需求要你显示一个完整名字。

select concat(first_name, last_name)
 as 'full_name' from account

    但是你发现大部分人的名字都不显示了，你查到这些人的first_name都为null，concat出来的结果也为null。

目的：辨别悬空值

    不可避免，数据库中总有些字段是没有值的。SQL支持一个特殊的值，那就是NULL。本章的目的就是要弄清楚如何编写那些包含NULL的查询。

反模式：将NULL作为普通的值，反之亦然

    开发者都对SQL中的NULL的行为感到茫然无措。SQL将NULL当作一个特殊值，不同于0、false或空字符串。但在Oracle的Sybase中，NULL的意义是长度为0的空字符串。

在表达式中使用NULL

    在值为NULL的列上进行计算所得的结果也是NULL，NULL和0是不同的

select hour + 10 from bug;

    以上语句你可能期待，当值为NULL时返回10，但是实际上它返回的NULL
    NULL和false也是不同的，and、or和not这三个布尔操作如果涉及NULL，其结果也让人感到困惑。

搜索允许为空的列

    如果以下查询返回assigned_to为123的行，不包含别的值和NULL：

select * from bug where assigned_to = 123;

你可能觉得以下查询会返回上个查询的补集，也就是所有之前查询没有返回的行：

select * from bug where not (assigned_to = 123)

然而，这个查询都不会返回为NULL的记录。任何和NULL比较都返回“未知”，既不是TRUE，也不是FALSE。如下都得不到你需要的结果

select * from bug where assigned_to = NULL;
select * from bug where assigned_to <> NULL;

在查询参数中使用NULL

select * from bug assigned_to = ?

    你传入一个整型时，会返回你期望的值，但是你不能传入一个NULL。这永远得不到为NULL的行。

避免上述问题

    为了避免上述问题，很多程序员会定义一个固定的值来取代NULL值。但是这样又会造成以下问题。

create table bug(
	...
	assigned_to not null,
	hour numeric(9,2) not null default -1
);

    假如定义hour的悬空值为-1，但是你有没有想过当你SUM，AVG这一列时，会有什么后果。因而你又必须添加一个额外的条件去除这个值

select SUM(hours) from bug where hours <> -1

    接着来看assigned_to，这个外键指向account，当一个bug提交了，还没有人处理，assigned_to应该怎么填写呢？如果你因此去创建一个没有意义的账号，这看起来多么可笑。

如何识别反模式：

    当出现以下情况时，可能是反模式

1. 我如何将没有值（Null）的列取出来？
2. 将字符串与Null进行拼接操作，结果返回Null

合理使用反模式

    反模式不是使用NULL，反模式是将NULL作为一个普通值处理或者使用一个普通的值来代替NULL。
    有一种情况可以将NULL视为普通值，那就是导入或者导出数据的时候。

解决方案：将NULL作为特殊值

    你需要理解SQL的三值逻辑。true、false和NULL。

在标量表达式中使用NULL

    假如Bob的年龄未知，Alice的年龄也未知，我问你，他两谁的年纪大，你肯定回答不知道。那么这就是NULL = NULL的结果是NULL。
    下表列举一些程序员期望得到某种结果，但是不如人意的情况。

在布尔表达式中使用NULL

    下表列举一些程序员期望得到某种结果，但是不如人意的情况。

检索NULL值

    你需要使用is null等语句

select * from bug where assigned_to is null;
select * from bug where assigned_to is not null;

    在SQL-99标准中，定义了一个比较断言 is distinct from，它在操作NULL时，会返回true或者false。以下两个查询等价

select * from bug where assigned_to is null or assigned_to <> 1;
select * from bug where assigned_to is distinct from 1;

    你也可以将NULL做为参数传入

select * from bug where assigned_to is distinct from ?;

声明NOT NULL的列

    如果NUll会破坏程序逻辑或NULL本身就是毫无意义的，那么使用NOT NULL吧。比如bug表中的date_report, report_by, status。
    如果有人建议你使用default值，这并不是通用的，比如report_by应该总是非NULL的，如果要定义默认值，那么定义什么默认值。

动态默认值

    如果你只是某一请求需要使用默认值，那么你就在查询时使用COALESCE()，或者IFNULL()

select concat(IFNULL(first_name, ''), IFNULL(last_name, '')) as 'full_name' from account;

总结

    接受NULL来表示任意类型的悬空值