[SQL Server]NULLと０または空文字を除外する

[スポンサードリンク]

「NULLと0」または「NULLと空文字」を除外する

下記のソースは
「数値項目 IS NOT NULL AND 数値項目 != 0」
「文字列項目 IS NOT NULL AND 文字列項目 != ”」
を簡潔にし、かつバグを防ぐものである。

論理演算子を使わずに、ISNULLで簡潔にする。

よくあるバグ

これは仕様バグの場合もあるし、コーディングミスの場合もある。

仕様書には「NULL以外」や「IS NOT NULL」と書かれている場合でも、実際のテーブルの値は「NULL」ではなく空文字「”」の場合だったり。

空文字「”」の想定で仕様書が書かれていても、外部結合などによる影響で結果が「NULL」である場合。

また、そのどちらのケースも同時に発生する場合がある。

このとき、NULLや0、空文字を除外して計算またはデータを取得しなければならない場合の対処方法を記載する。

実際に、プログラマにより、「NULLと0」または「NULLと空文字」に対応していたり、NULLまたは空文字（または0）のみに対応していたり、バラバラな場合があり、これがバグを生む。

NULLと0

「NULLと0」であるかを判断する

DECLARE @table table (
    ID      int,        -- ID
    ITEM_NO varchar(4), -- 商品No
    QTY     int         -- 在庫数
)

INSERT INTO @table VALUES (1,'0001',null)
INSERT INTO @table VALUES (2,'0002',0)
INSERT INTO @table VALUES (3,'0003',45)

SELECT
    ID,
    ITEM_NO,
    QTY,
    CASE WHEN ISNULL(QTY,0) = 0
        THEN '0かNULL'
        ELSE '0かNULL以外'
    END AS HANTEI
FROM
    @table

DECLARE @table table (

ID int, -- ID

ITEM_NO varchar(4), -- 商品No

QTY int -- 在庫数

)

INSERT INTO @table VALUES (1,'0001',null)

INSERT INTO @table VALUES (2,'0002',0)

INSERT INTO @table VALUES (3,'0003',45)

SELECT

ID,

ITEM_NO,

QTY,

CASE WHEN ISNULL(QTY,0) = 0

THEN '0かNULL'

ELSE '0かNULL以外'

END AS HANTEI

FROM

@table

「NULLと0」を除外する

下記のソースコードは
「WHERE QTY IS NOT NULL AND QTY != 0」
を簡潔にするものである。

DECLARE @table table (
    ID      int,        -- ID
    ITEM_NO varchar(4), -- 商品No
    QTY     int         -- 在庫数
)

INSERT INTO @table VALUES (1,'0001',null)
INSERT INTO @table VALUES (2,'0002',0)
INSERT INTO @table VALUES (3,'0003',45)

SELECT
    ID,
    ITEM_NO,
    QTY,
    CASE WHEN ISNULL(QTY,0) = 0
        THEN '0かNULL'
        ELSE '0かNULL以外'
    END AS HANTEI
FROM
    @table
WHERE
    ISNULL(QTY,0) != 0

DECLARE @table table (

ID int, -- ID

ITEM_NO varchar(4), -- 商品No

QTY int -- 在庫数

)

INSERT INTO @table VALUES (1,'0001',null)

INSERT INTO @table VALUES (2,'0002',0)

INSERT INTO @table VALUES (3,'0003',45)

SELECT

ID,

ITEM_NO,

QTY,

CASE WHEN ISNULL(QTY,0) = 0

THEN '0かNULL'

ELSE '0かNULL以外'

END AS HANTEI

FROM

@table

WHERE

ISNULL(QTY,0) != 0

NULLと空文字

「NULLと空文字」であるかを判断する

DECLARE @table table (
    ID      int,        -- ID
    ITEM_NO varchar(4), -- 商品No
    RESULT  varchar(2)  -- 検査結果（NULLや空文字は未検査）
)

INSERT INTO @table VALUES (1,'0001',null)
INSERT INTO @table VALUES (2,'0002','')
INSERT INTO @table VALUES (3,'0003','OK')
INSERT INTO @table VALUES (4,'0004','NG')

SELECT
    ID,
    ITEM_NO,
    RESULT,
    CASE WHEN ISNULL(RESULT,'') = ''
        THEN '空文字かNULL'
        ELSE '空文字かNULL以外'
    END AS HANTEI
FROM
    @table

DECLARE @table table (

ID int, -- ID

ITEM_NO varchar(4), -- 商品No

RESULT varchar(2) -- 検査結果（NULLや空文字は未検査）

)

INSERT INTO @table VALUES (1,'0001',null)

INSERT INTO @table VALUES (2,'0002','')

INSERT INTO @table VALUES (3,'0003','OK')

INSERT INTO @table VALUES (4,'0004','NG')

SELECT

ID,

ITEM_NO,

RESULT,

CASE WHEN ISNULL(RESULT,'') = ''

THEN '空文字かNULL'

ELSE '空文字かNULL以外'

END AS HANTEI

FROM

@table

「NULLと空文字」を除外する

下記のソースコードは
「WHERE RESULT IS NOT NULL AND RESULT != ” 」
を簡潔にするものである。

DECLARE @table table (
    ID      int,        -- ID
    ITEM_NO varchar(4), -- 商品No
    RESULT  varchar(2)  -- 検査結果（NULLや空文字は未検査）
)

INSERT INTO @table VALUES (1,'0001',null)
INSERT INTO @table VALUES (2,'0002','')
INSERT INTO @table VALUES (3,'0003','OK')
INSERT INTO @table VALUES (4,'0004','NG')

SELECT
    ID,
    ITEM_NO,
    RESULT,
    CASE WHEN ISNULL(RESULT,'') = ''
        THEN '空文字かNULL'
        ELSE '空文字かNULL以外'
    END AS HANTEI
FROM
    @table
WHERE
    ISNULL(RESULT,'') != ''

DECLARE @table table (

ID int, -- ID

ITEM_NO varchar(4), -- 商品No

RESULT varchar(2) -- 検査結果（NULLや空文字は未検査）

)

INSERT INTO @table VALUES (1,'0001',null)

INSERT INTO @table VALUES (2,'0002','')

INSERT INTO @table VALUES (3,'0003','OK')

INSERT INTO @table VALUES (4,'0004','NG')

SELECT

ID,

ITEM_NO,

RESULT,

CASE WHEN ISNULL(RESULT,'') = ''

THEN '空文字かNULL'

ELSE '空文字かNULL以外'

END AS HANTEI

FROM

@table

WHERE

ISNULL(RESULT,'') != ''

[スポンサードリンク]

IT☆ぱらだいす

全てのITエンジニアの役に立ちたい