SQL 中的 NOT EXISTS 子句

NOT EXISTS clause in SQL

提问by user2106410

I have been stuck on a query and I am really not able to think how does the execution takes place, any help will be highly appreciated :

我一直被困在一个查询上，我真的无法思考执行是如何发生的，任何帮助将不胜感激：

The query is devised to find the details of the employee who works on all the projects.

该查询旨在查找参与所有项目的员工的详细信息。

The query is :

查询是：

SELECT E.LNAME, E.FNAME
FROM EMPLOYEE E
WHERE NOT EXISTS
(
    SELECT PNUMBER
    FROM PROJECT
    WHERE PNUMBER NOT EXISTS 
    (
        SELECT PNO 
        FROM WORKS_ON
        WHERE ESSN=E.SSN 
    ) 
);

DB Structure is :

数据库结构是：

Table Projects with columns :

带有列的表项目：

Pname,Pnumber,Plocation and dnum

Pname、Pnumber、Plocation 和 dnum

Table works_on with columns :

带有列的表works_on：

ESSN,PNO and HOURS

ESSN、PNO 和小时

Table Employee with columns :

带有列的表员工：

Fname,minit,Lname,SSN,Bdate,address, sex,salary,superssn and dno

Fname,minit,Lname,SSN,Bdate,address,sex,salary,superssn 和 dno

If someone can explain in simple words how this query executes it will be really helpful.

如果有人能用简单的话解释这个查询是如何执行的，那将非常有帮助。

回答by KeyszerS

The SQL EXISTS condition is considered "to be met" if the subquery returns at least one row.

如果子查询至少返回一行，则认为 SQL EXISTS 条件“满足”。

Therefore, by implying NOT EXISTS, we want the subquery to return zero rows, so with that knowledge let's look at your query

因此，通过暗示 NOT EXISTS，我们希望子查询返回零行，因此有了这些知识，让我们看看您的查询

SELECT E.LNAME, E.FNAME
FROM EMPLOYEE E
WHERE NOT EXISTS (SELECT PNUMBER
FROM PROJECT
WHERE PNUMBER NOT EXISTS (SELECT PNO 
FROM WORKS_ON
WHERE ESSN=E.SSN ) );

There are two nested NOT EXISTS statement, and SQL will have to run them in reverse order, because one relies on the other. The first one which will be queried is this one (the last one):

有两个嵌套的 NOT EXISTS 语句，SQL 必须以相反的顺序运行它们，因为一个依赖于另一个。第一个将被查询的是这个（最后一个）：

SELECT PNO 
    FROM WORKS_ON
    WHERE ESSN=E.SSN

If this returns zero rows (because we've said NOT EXISTS), then it will run the next query, which will be:

如果这返回零行（因为我们已经说过 NOT EXISTS），那么它将运行下一个查询，这将是：

SELECT PNUMBER
    FROM PROJECT

Again, this has to return zero rows, and if it does, then it will run the final query, which is the first one.

同样，这必须返回零行，如果确实如此，则它将运行最终查询，即第一个查询。

SELECT E.LNAME, E.FNAME
FROM EMPLOYEE E

In essence, every "NOT EXIST" subquery has to return zero rows for the preceding query to run, otherwise you will end up with 0 rows (no results).

本质上，每个“NOT EXIST”子查询都必须返回零行才能运行前面的查询，否则最终会得到 0 行（没有结果）。

More information about the EXISTS condition here

有关 EXISTS 条件的更多信息，请点击此处

回答by neXus

I know this is an old question but I was intrigued so I spent some time on it and it would be a waste to let the effort be lost.

我知道这是一个老问题，但我很感兴趣，所以我花了一些时间在它上面，让努力付之东流是一种浪费。

First of all, I don't know the syntax of the inner <column name> NOT EXISTS <subquery>but it seems to be equivalent to <column name> NOT IN <subquery>. Just this notion made the query more comprehensive for me because it more clearly links the inner query to the PROJECT.
So I started from

首先，我不知道内部的语法，<column name> NOT EXISTS <subquery>但它似乎相当于<column name> NOT IN <subquery>. 正是这个概念使我的查询更加全面，因为它更清楚地将内部查询链接到PROJECT。
所以我从

SELECT E.LNAME, E.FNAME
FROM EMPLOYEE E
WHERE NOT EXISTS
(
    SELECT PNUMBER
    FROM PROJECT
    WHERE PNUMBER NOT IN
    (
        SELECT PNO
        FROM WORKS_ON
        WHERE ESSN=E.SSN
    )
);

Breakdown of the steps:

步骤分解：

• The inner query simply lists all the project numbers that your employee works on.

          SELECT PNO
          FROM WORKS_ON
          WHERE ESSN=E.SSN
  

• The middle query takes the complement. The result is all the project numbers that your employee does not work on.

      SELECT PNUMBER
      FROM PROJECT
      WHERE PNUMBER NOT IN
      (
          -- projects that the employee works on
      )
  

• If there are projects existing where the employee doesn't work on then he doesn't work on all of them and he should therefor not be included in the results.

  SELECT E.LNAME, E.FNAME
  FROM EMPLOYEE E
  WHERE NOT EXISTS
  (
      -- projects that the employee does not work on
  )
  

• 内部查询仅列出您的员工所从事的所有项目编号。

          SELECT PNO
          FROM WORKS_ON
          WHERE ESSN=E.SSN
  

• 中间查询采用补码。结果是您的员工不参与的所有项目编号。

      SELECT PNUMBER
      FROM PROJECT
      WHERE PNUMBER NOT IN
      (
          -- projects that the employee works on
      )
  

• 如果存在员工不工作的项目，那么他不会在所有项目上工作，因此他不应该被包含在结果中。

  SELECT E.LNAME, E.FNAME
  FROM EMPLOYEE E
  WHERE NOT EXISTS
  (
      -- projects that the employee does not work on
  )
  

回答by Mohammed Ismail

The answer by keyszersis more than sufficient for this question. However I would like to add few more points

对于这个问题，keyzers的回答已经绰绰有余。不过我想补充几点

The execution of SQL query starts from the inner query. Hence in your Query the one in where clause executes first

SQL 查询的执行从内部查询开始。因此，在您的查询中， where 子句首先执行

WHERE PNUMBER NOT EXISTS ( SELECT PNO FROM WORKS_ON WHERE ESSN=E.SSN )

PNUMBER 不存在的地方（从 WORKS_ON WHERE ESSN=E.SSN 中选择 PNO）

based on the result of above query the other query below will run.

基于上述查询的结果，下面的另一个查询将运行。

WHERE NOT EXISTS (
    SELECT PNUMBER
    FROM PROJECT
    WHERE PNUMBER NOT EXISTS (**Resultset**)

To summarise my point - You need to debug your query based on the resultsets in the subqueries.

总结我的观点 - 您需要根据子查询中的结果集调试查询。