1. oracle中把有null值的字段當(dāng)做where查詢條件時呐萌，有null的數(shù)據(jù)會返回false
- 原數(shù)據(jù)：
SELECT * FROM GGS."student02"
原數(shù)據(jù)
- null值的字段當(dāng)做where條件：
SELECT * FROM GGS."student02" a WHERE exists (SELECT * FROM GGS."student02" b WHERE a."age" =b."age" AND a."name" = b."name")
null值的字段當(dāng)做where條件
- 同樣一張表，age = null 的數(shù)據(jù)沒有了
2.使用函數(shù)：nvl(字段温峭，值)，當(dāng)指定字段為null時彭则，以值代替null進(jìn)行比較
SELECT * FROM GGS."student02" a WHERE exists (SELECT * FROM GGS."student02" b WHERE NVL(a."age", 0) = NVL(b."age", 0) AND a."name" = b."name")
nvl(字段棱烂，值)
- 測試數(shù)據(jù)
CREATE TABLE "GGS"."student02" (
"name" VARCHAR2(255 BYTE) ,
"age" NUMBER
);
INSERT INTO "GGS"."student02" VALUES ('李四', '21');
INSERT INTO "GGS"."student02" VALUES ('王五', '22');
INSERT INTO "GGS"."student02" VALUES ('張三', NULL);
INSERT INTO "GGS"."student02" VALUES ('趙六', NULL);
INSERT INTO "GGS"."student02" VALUES ('張三', '20');
