第六章 8-Exists运算符|the exists operator--Mosh my SQL

1, select clients that have an invoice

可以用In或者join

select *
from clients
where client_id in (
    select distinct client_id
    from invoices
    )

或者

SELECT *
FROM clients c1
JOIN (
SELECT DISTINCT client_id FROM invoices
) c2
ON c1.client_id = c2.client_id

我们还可以用今天要学的Exists运算符。如果客户ID非常多，可以节省效率，会妨碍最佳性能

select *
from clients c
where exists (
    select distinct client_id
    from invoices
    where client_id=c.client_id
    )

2，练习：找到没有被订购的产品，假设你有网站比如亚马逊，用in会返回很多结果集

答案：

如果用in

select *
from products
where product_id not in(
    select product_id
    from order_items
    )

如果用exists

select *
from products p
where not exists (
    select product_id
    from order_items
    where product_id =p.product_id
    )