为什么在SQL中不可避免地要使用双重嵌套的NOT EXISTS语句 [英] Why are double nested NOT EXISTS statements unavoidable in SQL

问题描述

这更多是出于好奇/科学兴趣，而不是基于实际问题，我曾经问过数据库讲师这件事，但是他无法回答/理解我的问题.所以我决定过来这里.

This is more out of curiosity/scientific interest, than based on a real problem and I once asked my databases lecturer about this, but he could not answer/understand my question. So I decided to come over here.

编程语言应该是一种工具，并且可以简化工作，对吗?因此，为什么只需执行SELECT * FROM foo WHERE bar=42;就可以在一个表中找到所有条目.但是，一旦涉及到多个表，就没有简单/直观的方式说找到所有满足此条件的元组"了吗?

A programming language is supposed to be a tool and tools are made to make working easier, right? So why is it that you can find all entries in a one table by simply doing SELECT * FROM foo WHERE bar=42;. But as soon as there are multiple tables involved, there is no easy/intuitive way to say "find all tuple meeting this criteria"?

脚本中给定的示例是这样的(这是德语翻译的，因此命名可能会有些混乱):

The given example in the script is something like this (this is translated from German, so the naming might be a bit confusing):

对于以下格式的表格:

• 组件( CNR ，名称，颜色，重量，城市)
• 项目( PNR ，Pname，城市)
• scp( SNR ， CNR ， PNR ，数量)

• component(CNR,Cname,Color,Weight,City)
• project(PNR,Pname,City)
• scp(SNR,CNR,PNR,Quantity)

主键以粗体显示.该示例是关于将组件运送到不同城市中的不同项目的.

Primary keys in bold. The example is about components which are shipped to different projects in different cities.

任务是编写一个查询，以查找/已运送到一个特定城市中所有项目的所有组件.

The task is to write a query to find all components that are/were shipped to all projects in one specific city.

给定的解决方案如下:

SELECT CNR
FROM components
WHERE NOT EXISTS ( SELECT 1
                  FROM project
                  WHERE project.city = 'Foobar Town'
                  AND NOT EXISTS ( SELECT 1
                                 FROM scp
                                 WHERE scp.PNR = projekt.PNR
                                 AND scp.CNR = components.CNR ));

我的意思是，这是正确的，有道理，甚至可行.但这不是直观的，当然也不能使生活变得更轻松！那么我想知道这个原因在哪里呢?由于我们被告知对考试至关重要，因此我们能够编写这样的查询，因此我无法绕开它.

I mean yes it is correct, makes sense and even works. But it is not intuitive and certainly does not make life easier! So where is the reason for this I wonder? Since we were told that it is crucial for the exams, that we are able to write such queries I there is no way around learning it.

我还没有找到一个更简单的解决方案.既不能通过谷歌搜索，也不能自己尝试.一定有比每个人都懒得解决这个问题"更好的理由.有任何想法吗?

I haven't been able to find a simpler solution to this yet. Neither by googling nor trying by myself. There must be a better reason to this than "everybody is too lazy to fix this". Any one any ideas?

示例和本课程中提供给学生的所有材料均基于SQL92.

The example and all the material given to the students in our course is based on SQL92.

谢谢您的回答

推荐答案

您的问题是:查找/运送到一个特定城市中所有项目的所有组件."您将其改写为查找在给定城市中没有没有项目的项目中的所有项目."

Your question is: "Find all components that are/were shipped to all projects in one specific city." You are rephrasing this as "Find all components where there is no project in a given city that doesn't have the component."

我更倾向于直接回答:

select scp.component
from scp join
     projects p
     on scp.pnr = p.pnr
where p.city = 'Foobar Town'
group scp.component
having count(distinct scp.pnr) = (select count(distinct pnr)
                                  from projects
                                  where city = 'Foobar Town'
                                 );

这将计算城市中不同项目的数量，并将其与城市中项目的数量进行比较(distinct id在子查询中可能不是必需的.

This counts the number of distinct projects in the city and compares them to the number of projects in the city (the distinct id probably not necessary in the subquery.

首先，我不确定这是否更简单.其次，我是第一个承认NOT EXISTS方法 可能更有效的方法，尽管NOT EXISTS在子查询中的嵌套可能会损害性能.但是，我确实认为这更容易理解.

First, I'm not sure if this is simpler. Second, I'm the first to admit that the NOT EXISTS method may be more efficient, although the nesting of NOT EXISTS in subqueries may be detrimental to performance. I do, however, think that this is easier to follow.

这篇关于为什么在SQL中不可避免地要使用双重嵌套的NOT EXISTS语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！