今天在做一些oracle sql查询练习,突然遇到如下问题:
查询使用的实例表结构是oracle内置用户hr下的employees表,我的目标是查出哪些员工跟Pat(FIRST_NAME)、Fay(LAST_NAME)不在同一个部门(非关联子查询)。
我使用如下关联查询语句(1号):
SELECT e.first_name || ' ' || e.last_name AS 姓名,
e.department_id AS 部门号
FROM employees e
WHERE NOT EXISTS (SELECT 1
FROM employees e1
WHERE e1.department_id = e.department_id
AND e1.first_name = 'Pat'
AND e1.last_name = 'Fay');
查询结果如下图:
而当我使用如下查询语句时(2号):
SELECT e.first_name || ' ' || e.last_name AS 姓名,
e.department_id AS 部门号
FROM employees e
WHERE EXISTS
(SELECT 1
FROM employees e1
WHERE e1.first_name = 'Pat'
AND e1.last_name = 'Fay'
AND e1.department_id <> e.department_id);
查询结果却是这样:
逻辑并没有错,可为什么差了一条,将两个结果进行minus,得出相差的那条记录为
这条记录部门号为NULL,终于明白是有NULL参与比较导致的。
又使用如下代码进行了实验:
SELECT CASE
WHEN NULL = NULL OR NULL <> NULL OR NULL = 'a' OR 'a' > NULL OR
'a' < NULL THEN
'a'
WHEN 'b' is not null and '' is NULL THEN
'b'
ELSE
'c'
END
FROM dual;
得出结果为:b,说明有NULL参与的比较运算得到的都是false。
于是最终2号将代码改为:
SELECT e.first_name || ' ' || e.last_name AS 姓名,
e.department_id AS 部门号
FROM employees e
WHERE EXISTS
(SELECT 1
FROM employees e1
WHERE e1.first_name = 'Pat'
AND e1.last_name = 'Fay'
AND NVL(e1.department_id,0) <> NVL(e.department_id,0));
得到的记录数与1号代码一致。
结论:对存在NULL字段的进行比较时,应该注意去空。