给定情况下SQL中的查询优化

Question

以下是给定的表格：

create table products 
(productID int not null auto_increment,
Name varchar(30),
Price float ,
CoffeOrigin varchar(30),
primary key (productID));
create table customers
(customerID int not null auto_increment,
First_Name varchar(30),
Last_Name varchar(30),
Gender varchar(2) not null CHECK (Gender IN ('M', 'F')),
ContactNumber bigint,
primary key (customerID));
create table orders
(orderID int not null auto_increment,
productID int,
customerID int,
Date_Time datetime,
primary key(orderID),
foreign key(customerID) references customers(customerID),
foreign key(productID) references products(productID));

问题是：

编写一个优化的查询，找出所有订购的customerID的名称原产于"哥斯达黎加"或"印度尼西亚"的咖啡。

我的尝试：

select customers.customerID, first_name, last_name from customers where customerID in
(select customerID from orders where productid in
(select productid from products where coffeorigin = "costa rica" or "indonesia"));

我的老师说它可以优化得更多，但我看不出有任何办法。请帮帮我。

Answer 1

与其使用这些嵌套的in子查询，我建议使用exists和具有join:的相关子查询

select c.customerID, c.first_name, c.last_name 
from customers c 
where exists (
select 1
from orders o
inner join products p on p.productID = o.productID
where p.coffeorigin in ('costa rica', 'indonesia') and o.customerID = c.customerID
);