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
)