如何在MySQL中使用where exists或not exists实现交集

Question

我的表格如下：

customer(cid,name,city,state)
orders(oid,cid,date)
product(pid,productname,price)
lineitem(lid,pid,oid,number,totalprice)

我希望能够选择所有在城市“X”居住的客户购买的产品。这意味着我需要取交集，即所有居住在城市'X'的客户购买的产品。

例如：如果有三个客户c1，c2和c3，则我的答案是c1.product（交集）c2.product（交集）c3.product

我想仅使用where exists或where not exists来实现这一点，因为我需要编写关系演算。其中where not in 或 where in不可用。我的部分查询如下：

select 
  * 
from 
  product p,
  lineitem l,
  customer c1 
where 
  exists(
   select 
      * 
   from 
     customer c,
     orders o 
   where 
    o.cid=c.cid and 
    c.city='X' and 
    l.oid=o.oid and 
    l.pid=p.pid and 
    c1.cid=c.cid)

上面的查询给我提供了城市X中所有客户的pid、cid、oid、lid、总价格、城市、产品名称。现在我需要弄清楚如何选择所有客户共同拥有的产品。

注意：

我不能使用任何聚合函数，因为在关系演算中不可用。我有一个使用聚合函数的工作查询，那就是：

select 
   p.productname 
from 
   product p, 
   orders s, 
   lineitem l, 
   customer c 
where 
   l.pid=p.pid and
   l.oid=s.oid and 
   c.cid=s.cid and 
   c.city='X' 
group by 
   p.productname 
having 
   count(distinct c.cid)=(select count(*) from customer c1 where c1.city='X')

如果有人能够将上述查询转换为where exists或where not exists形式，而不使用count和group by，那么没问题。

我相信这是可能的，因为我可以在关系代数中做到这一点，并且根据科德定理，元组关系演算和关系代数在逻辑上是等价的，任何在其中一个中表达的查询都可以在另一个中表达。由于关系代数和关系演算都不支持聚合函数，查询可以在sql中表达而不使用聚合函数。

- user1613360

---

你想要的是关系除法，NOT EXISTS 基本上是 Codd 的查询，参见 https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/。 - dnoeth

Answer 1

这是我的答案。

我在http://www.sqlfiddle.com/#!2/f2fb85/1创建了一个sqlfiddle，所以您可以尝试它。

查询如下：

SELECT p.*
FROM product p
WHERE NOT EXISTS (
    SELECT c.cid
    FROM customer c
    WHERE NOT EXISTS (
        SELECT l.lid
        FROM lineitem l
        JOIN orders o ON o.oid = l.oid
        WHERE l.pid = p.pid
        AND o.cid = c.cid
    )
    AND c.city = 'X'
) AND EXISTS (
    SELECT c.cid
    FROM customer c
    WHERE c.city = 'X' 
)

- marcosh

---

我认为 AND EXISTS 的第二部分是多余的。做得好。 - Mehran

你可以在你提供的fiddle中轻松测试这个。我也怀疑过，但相信我，这不是必要的。 - Mehran

你的查询语句的第一部分是这样说的：选择所有产品，其中没有任何客户未购买该产品。我相信这就是所需的全部内容。 - Mehran

@marcosh 仔细一想，如果城市中没有客户，那么这个新查询会返回所有产品！因为我们说“如果没有没有购买的客户”，如果根本没有客户，那么肯定就没有未购买的人！很抱歉要说需要一个“AND”条件以确保在该城市存在某些客户。 - Mehran

@Mehran 我刚刚重用了我的以前的版本，抱歉。现在我将编辑它，希望这是最后一个正确的版本。 - marcosh

Answer 2

ANSI-Sql缺乏集合理论全称量词，你需要使用重写规则来获得存在量词：

∀ Universal quantifier (For all . . .)
∃ Existential quantifier (There exists . . .)

例子：

(∀c ∈ CUSTOMER)
⇔ /*Add double negation */
¬¬(∀c ∈ CUSTOMER)
⇔ /*Bring one negation into the quantification, quantifier changes */
¬(∃c ∈ CUSTOMER)

SQL翻译：

SELECT p.*
FROM product p
WHERE NOT EXISTS (
  SELECT c.cid
  FROM customer c
  WHERE  c.city = 'X'
  AND NOT EXISTS
  (
    SELECT o.oid
    FROM orders o
    JOIN lineitem l ON l.oid = o.oid
    WHERE l.pid = p.pid
    AND o.cid = c.cid
  )
)

小提琴演示

- Mohsen Heydari

Answer 3

根据您的评论，我已用不使用IN的方法替换了之前的答案。这个答案使用了多个级别的相关子查询：

select p.* 
from product p
where exists (
  select *
  from customer c
  where c.city = 'x'
    and exists (
      select *
      from lineitem l
      where l.pid = p.pid
        and exists (
          select *
          from orders o
          where o.oid = l.oid
            and o.cid = c.cid
        )
    )
)
  and not exists(
    select *
    from customer c
    where c.city = 'x'
      and not exists (
        select *
        from lineitem l
        where l.pid = p.pid
          and exists (
            select *
            from orders o
            where o.oid = l.oid
              and o.cid = c.cid
          )
    )
  )

在这里查看SQL Fiddle

- Jeff Rosenberg

---

谢谢，但我不想使用where in和not in语句，因为我希望将其转换为关系演算中，在这些语句不可用的情况下只有where exist和where not exists是可用的。 - user1613360

嗯，exists和in在实现上非常相似，所以我会尝试使用exists来重写它。 - Jeff Rosenberg

Answer 4

select * from product p where not exists(
    select *
    from customer c
    where c.city = 'x'
      and not exists (
        select *
        from lineitem l
        where l.pid = p.pid
          and exists (
            select *
            from orders o
            where o.oid = l.oid
              and o.cid = c.cid
          )
    )
  )

关系演算：

{T| ∃pЄproduct (¬(∃cЄcustomer(c.city="Newark")^¬(∃lЄlineitem(l.pid=p.pid)^∃oЄorders(o.oid=l.oid^o.cid=c.cid))))}

关系代数：

- user1613360