Merge语句中NULL的陷阱

NULL表示unknown，不确定值，所以任何值（包括null值）和NULL值比较都是不可知的，在on子句，where子句，Merge或case的when子句中，任何值和null比较的结果都是false，这就是NULL设下的陷阱，我被坑过。

有一次，我使用Merge同步数据，由于target表中存在null值，虽然在source表中对null值做过处理，但是忽略了target表中的null值，导致数据merge失败。

step1，创建示例数据

--create source table

create table dbo.dt_source

(

id int null,

code int null

)

on [primary]

with(data_compression=page)

--create target table

create table dbo.dt_target

(

id int null,

code int null

)

on [primary]

with(data_compression=page)

step2，插入示例数据

示例数据中，Source表和Target表中都存在null值，不管是在Source表，还是在Target表，都要避免和null值进行比较。

--insert data into table

insert into dbo.dt_source(id,code)

values(1,1),(2,2),(3,null)

insert into dbo.dt_target(id,code)

values(1,1),(2,null)

step3，错误写法：只处理Source表中的null，而忽略Target表中的null

-- -1 stand for unknwon value

merge dbo.dt_target t

using dbo.dt_source s

    on t.id=s.id

when matched and( t.code<>isnull(s.code,-1))

    then update

        set t.code=s.code

when not matched

    then insert(id,code)

    values(s.id,s.code);

查看Target和Srouce表中的数据，数据不同步，不同步的原因是when matched子句之后的and 条件， t.code中存在null值，null值和任何值（包括null值）比较的结果都是unknown，在when子句中视为false。

正确写法1，不管是在target表，还是在source表，只要存在null值，必须进行处理，避免出现和null进行比较的情况。

处理的方式是使用一个值来表示unknwon，如果ID列有效值不可能是负值，那么可以使用-1来代替unknown。因为-1和-1 是相等的，逻辑上就将null值和null值视为相同。

-- -1 stand for unknwon value

merge dbo.dt_target t

using dbo.dt_source s

    on t.id=s.id

when matched and( isnull(t.code,-1)<>isnull(s.code,-1))

    then update

        set t.code=s.code

when not matched

    then insert(id,code)

    values(s.id,s.code);

正确写法2，在条件子句中，使用is null或 is not null来处理null值。

Tsql 使用is null和is not null来确实是，不是 null。 null is null 的逻辑值是true，other_value is null 为false， other_value is not null 为true。

merge dbo.dt_target t

using dbo.dt_source s

    on t.id=s.id

when matched and( t.code<>s.code or t.code is null or s.code is null)

    then update

        set t.code=s.code

when not matched

    then insert(id,code)

    values(s.id,s.code);

巴特西

Merge语句中NULL的陷阱

最新文章

热门文章