Query Optimization in SQL in given case

Question

These are the given tables:

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));

Question is:

Write a optimized query to find out the name of all the customerIDs who ordered coffee that originated from ‘Costa Rica’ or ‘Indonesia’.

My attempt:

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"));

My teacher says it can be optimized more but I don't see any way. Please Help me out.

Answer 1

Rather than these nested in subqueries, I would recommend exists and a correlated subquery with a 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
);