mysql - 使用 not contains 代替 not in

Question

我有以下查询，它给出了正确的结果，但我想使用不存在而不是不存在。

 select cust_name from customer 
 where cust_id not in
    (select cust_id from ord where ord_id in
        (select ord_id from orderitem where prod_id in
            (select prod_id from product 
             WHERE PROD_DESCRIP = 'Guide to Tennis')))

此外我还尝试过 -

select cust_name from customer
where cust_id not exists
    (select cust_id from ord where ord_id in
        (select ord_id from orderitem where prod_id in
            (select prod_id from product
             WHERE PROD_DESCRIP = 'Guide to Tennis')))

不知道是真是假

Answer 1

您不要将列名放在NOT EXISTS之前，它只是WHERE NOT EXISTS(子查询)。

然后，您需要将其设为相关子查询，将外部表中的 cust_id 与内部表中的 cust_id 进行比较。

select cust_name
from customer AS c
where not exists
    (select 1 
     from ord AS o
     where ord_id in
        (select ord_id from orderitem where prod_id in
            (select prod_id from product WHERE PROD_DESCRIP = 'Guide to Tennis'))
        and o.cust_id = c.cust_id)

一般来说，使用JOIN比使用WHERE xxx IN更好——MySQL往往会更好地优化它。

select cust_name
from customer AS c
where not exists
    (select 1 
     from ord AS o
     join orderitem AS oi ON o.ord_id = oi.ord_id
     join product AS p ON oi.prod_id = p.prod_id
     where p.prod_descrip = 'Guide to Tennis'
        and o.cust_id = c.cust_id)

您可以使用LEFT JOIN 模式执行NOT EXISTS。

select cust_name
from customer AS c
left join ord AS o ON o.cust_id = c.cust_id
left join orderitem AS oi ON o.ord_id = oi.ord_id
left join product AS p ON oi.prod_id = p.prod_id AND p.prod_descrip = 'Guide to Tennis'
where o.cust_id IS NULL