以下两个查询会一致地给出相同的输出吗？

发布于 2024-12-23 07:11:28 字数 771 浏览 2 评论 0原文

我们想要找到那些拥有可以完成部门 20 员工完成的工作的员工的部门的部门号。

SELECT deptno
FROM   dept
WHERE  EXISTS(SELECT *
              FROM   emp x
              WHERE  x.deptno = 20
                     AND EXISTS(SELECT *
                                FROM   emp y
                                WHERE  y.job = x.job
                                       AND y.deptno = dept.deptno))
       AND deptno <> 20;

SELECT deptno
FROM   dept
WHERE  EXISTS(SELECT *
              FROM   emp x
              WHERE  x.deptno = dept.deptno
                     AND EXISTS(SELECT *
                                FROM   emp y
                                WHERE  y.job = x.job
                                       AND y.deptno = 20))
       AND deptno <> 20;

原文

We want to find the deptno of those departments having employees who can do some work done by employee in depertment 20.

SELECT deptno
FROM   dept
WHERE  EXISTS(SELECT *
              FROM   emp x
              WHERE  x.deptno = 20
                     AND EXISTS(SELECT *
                                FROM   emp y
                                WHERE  y.job = x.job
                                       AND y.deptno = dept.deptno))
       AND deptno <> 20;

SELECT deptno
FROM   dept
WHERE  EXISTS(SELECT *
              FROM   emp x
              WHERE  x.deptno = dept.deptno
                     AND EXISTS(SELECT *
                                FROM   emp y
                                WHERE  y.job = x.job
                                       AND y.deptno = 20))
       AND deptno <> 20;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

喜你已久 2024-12-30 07:11:28

是的，它们是等价的。它们也相当于：

SELECT deptno
FROM   dept
WHERE  EXISTS ( SELECT *
                FROM   emp x
                  JOIN emp y
                    ON y.job=x.job
                WHERE  x.deptno = 20
                  AND  y.deptno = dept.deptno 
              ) 
  AND  deptno <> 20;

Yes, they are equivalent. They are also equivalent to:

SELECT deptno
FROM   dept
WHERE  EXISTS ( SELECT *
                FROM   emp x
                  JOIN emp y
                    ON y.job=x.job
                WHERE  x.deptno = 20
                  AND  y.deptno = dept.deptno 
              ) 
  AND  deptno <> 20;

回复收藏 0 原文

靖瑶 2024-12-30 07:11:28

第一个查询查找部门 D，其中部门 20 中的员工 Y 与部门 D 中的员工 X 从事相同的工作。

第二个查询执行相同的操作，但 X 和 Y 交换了。所以我认为它们是等价的。

使用 join 而不是 notists 子查询，查询可能会更简单。例如，这在功能上是等效的：

select  distinct dept.deptno
from    dept
join    emp x
on      x.deptno = dept.deptno
join    emp y
on      y.deptno = 20
        and y.job = x.job
where   dept.deptno <> 20

The first query looks for department D with an employee Y in department 20 who has the same job as an employee X in department D.

The second query does the same but with X and Y switched. So I think they are equivalent.

The query is perhaps simpler with join instead of not exists subqueries. For example, this would be functionally equivalent:

select  distinct dept.deptno
from    dept
join    emp x
on      x.deptno = dept.deptno
join    emp y
on      y.deptno = 20
        and y.job = x.job
where   dept.deptno <> 20

回复收藏 0 原文

~没有更多了~