Trying to reduce number of table scans when querying on the same column but different values I'm having a mental block. I'm using a sample MySQL customer database...

http://www.mysqltutorial.org/mysql-sample-database.aspx

And would like to query for any customers that bought two products (product codes S18_1589 and S24_1628). I wrote this query...

SELECT customerNumber
FROM orders o
WHERE customerNumber IN (SELECT customerNumber
                         FROM orders o
                         JOIN orderdetails od
                         ON o.orderNumber = od.orderNumber
                         WHERE od.productCode = 'S18_1589')
AND customerNumber IN (SELECT customerNumber
                         FROM orders o
                         JOIN orderdetails od
                         ON o.orderNumber = od.orderNumber
                         WHERE od.productCode = 'S24_1628')
GROUP BY customerNumber
ORDER BY customerNumber;


That gets the results that I want but I'd like to do this without using two sub queries if possible. The customers can buy any other products but they need to have purchased the two mentioned.

In order to try to make the query more efficient, I changed it to this...

SELECT customerNumber
FROM orders o
JOIN orderdetails od
ON o.orderNumber = od.orderNumber
WHERE od.productCode IN ('S24_1628', 'S18_1589')
GROUP BY customerNumber
HAVING COUNT(*) &gt;= 2
ORDER BY customerNumber;


which gives me the same result as the first query but I think it's just a coincidence. I believe the query will also include customers that have purchased only one of the products but 2 or more times.

Anyone know a better way / more efficient way of handling?

NOTE: I was given this question and was told my query was not performant because of the two subqueries but no further feedback was given. It's a different dataset / schema than what I'm using now but size is similar and what I'm trying to query is the same type of question.
The join to 'Products' in both your sub queries is redundant and can be removed. You can filter directly on "Where od.ProductCode = 'S18_1589'" There is a FK between Products and OrderDetails, so there can't be a ProductCode that is not in Products.
The general challenge you are trying to solve is called 'Relational Division'. Your query will work for only 2 products, but what about 3, 10, or 30?? Search the forum here and you will find many excellent discussions on it. For your case, I would use the following solution. It relies on rephrasing the question in double negative form - "Show me all customers, for which there isn't a single ProductCode from our ProductsList, which is not in the customer's order items"



WITH prodctsList
AS
(
SELECT *
FROM   (VALUES ('S24_1628'), ('S18_1589')) AS products(productCode)
/* Add as many products as needed */
)
SELECT customerNumber
FROM   customers AS C
WHERE NOT EXISTS (
                  SELECT productCode
                  FROM   productList
                  EXCEPT
                  SELECT productCode
                  FROM   orders AS O
                         INNER JOIN
                         orderDetails AS OD
                         ON O.orderNumber = OD.orderNumber
                  WHERE  O.customerNumber = C.customerNumber
);



A solution without EXISTS and VALUES table constructor


WITH prodctsList
AS
(
    SELECT 'S24_1628' AS productCode
    UNION ALL
    SELECT 'S18_1589'
/* Add as many products as needed */
)
SELECT O.customerNumber
FROM   orders AS O
       INNER JOIN
       orderDetails AS OD
       ON O.orderNumber = OD.orderNumber
       INNER JOIN
       productList AS PL
       ON PL.productCode = OD.productCode
GROUP BY O.customerNumber
HAVING COUNT(*) = (SELECT COUNT(*) FROM productsList);