php - 在 SQL QUERY 不一致的 WHERE 子句中将 NOT IN 与 AND 连接

Question

这是我的失败。如果我运行:

SELECT pnumber, count(distinct ssn) as  num_emps,nvl(sum(hours),0) as thours, nvl(sum(hours*salary/2000),0) as tcost 
   FROM project 
    LEFT JOIN (works_on join (employee 
    LEFT JOIN Department on DNO=DNUMBER) on essn=ssn) 
   ON pnumber=pno 
GROUP BY pnumber 
ORDER BY pnumber

我明白

 PNUMBER   NUM_EMPS     THOURS      TCOST
---------- ---------- ---------- ----------
     1          4       67.5     1027.5
     2          3       37.5      562.5
     3          2         50        960
    10          3         55      762.5
    20          3         25      522.5
    30          4         60      967.5
    40          0          0          0
    50          0          0          0
    60          0          0          0

当我运行时:

SELECT pnumber, count(distinct ssn) as  num_emps,nvl(sum(hours),0) as thours, nvl(sum(hours*salary/2000),0) as tcost 
   FROM project 
    LEFT JOIN (works_on join (employee 
        LEFT JOIN Department on DNO=DNUMBER) on essn=ssn) 
    ON pnumber=pno 
WHERE  ssn IN (select MGRSSN from DEPARTMENT)
GROUP BY pnumber 
ORDER BY pnumber

我明白了

 PNUMBER   NUM_EMPS     THOURS      TCOST
---------- ---------- ---------- ----------
     1          1          5        100
     2          1         10        200
     3          1         10        200
    10          1         10        200
    20          3         25      522.5
    30          2         25        530

到目前为止一切顺利，让我们试试这个:

SELECT pnumber, count(distinct ssn) as  num_emps,nvl(sum(hours),0) as thours, nvl(sum(hours*salary/2000),0) as tcost 
   FROM project 
    LEFT JOIN (works_on join (employee 
        LEFT JOIN Department on DNO=DNUMBER) on essn=ssn) 
    ON pnumber=pno 
WHERE  ssn IN (select SUPERSSN from EMPLOYEE) AND ssn NOT IN (select MGRSSN from DEPARTMENT)
GROUP BY pnumber 
ORDER BY pnumber

我得到:

 PNUMBER   NUM_EMPS     THOURS      TCOST
---------- ---------- ---------- ----------
     1          1       32.5      487.5
     2          1        7.5      112.5

也很好，但是当我尝试这个时(我所做的只是添加另一个 Not in theWhere 子句):

SELECT pnumber, count(distinct ssn) as  num_emps,nvl(sum(hours),0) as thours, nvl(sum(hours*salary/2000),0) as tcost 
   FROM project 
   LEFT JOIN (works_on join (employee 
       LEFT JOIN Department on DNO=DNUMBER) on essn=ssn) 
   ON pnumber=pno 
WHERE  ssn NOT IN (select SUPERSSN from EMPLOYEE) AND ssn NOT IN (select MGRSSN from DEPARTMENT)
GROUP BY pnumber 
ORDER BY pnumber

我得到“没有选择任何行”!如何？应该还有 2 名员工不是 pnumber 1 的经理或主管。请帮忙？谢谢，

编辑

这是只有一个 NOT IN 的结果:

SQL> SELECT pnumber, count(distinct ssn) as  num_emps,nvl(sum(hours),0) as thours, nvl(sum(hours*salary/2000),0) as tcost
  2     FROM project
  3     LEFT JOIN (works_on join (employee
  4         LEFT JOIN Department on DNO=DNUMBER) on essn=ssn)
  5     ON pnumber=pno
  6  WHERE ssn NOT IN (select MGRSSN from DEPARTMENT)
  7  GROUP BY pnumber
  8  ORDER BY pnumber;

PNUMBER   NUM_EMPS     THOURS      TCOST
---------- ---------- ---------- ----------
     1          3       62.5      927.5
     2          2       27.5      362.5
     3          1         40        760
    10          2         45      562.5
    30          2         35      437.5

Answer 1

NOT IN不返回任何值为 NULL 的行。所以:

WHERE ssn NOT IN (select SUPERSSN from EMPLOYEE)

即使一行为 SUPERSSN 也会过滤掉所有内容如NULL 。这里有两种修复方法:

WHERE ssn NOT IN (select SUPERSSN from EMPLOYEE where SUPERSSN is not null)

或者:

WHERE NOT EXISTS (select 1 from EMPLOYEE e where e.SUPERSSN = ssn)

(您可能需要 ssn 上的别名，但我不知道它来自哪个表。)换句话说， NOT EXISTS 的语义。和NOT IN当有 NULL 时是不同的值(value)。 NOT EXISTS (在我看来)具有更直观的行为。

现在，如果其中一个值是 NULL，为什么它永远不会返回 true ？

WHERE ssn NOT IN (select SUPERSSN from EMPLOYEE)

如果ssn位于 SUPERSSN 的列表中，然后返回 false。那很容易。如果ssn不在列表中并且没有一个值是 NULL ，然后返回 true。这很容易。

如果ssn位于列表中且为 SUPERSSN 的值之一是 NULL ，那么就有一个难题。是ssn等于 NULL值与否。嗯，都不是。与 NULL 的比较返回NULL 。并且，NULL在 WHERE 中被视为“不真实”条款。简而言之，如果 SUPERSSN有一个NULL value，表达式只能返回 false 或 NULL -- 所有内容都会被过滤掉。