1

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(*) >= 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.

2
  • Looks like you can remove the products table completely from the query
    – eckes
    Commented Nov 18, 2018 at 20:32
  • Hi yes - I'll make that edit now. I had it in there because of using the product name originally
    – lg1
    Commented Nov 19, 2018 at 14:48

3 Answers 3

0

Try the following:

SELECT o1.customerNumber
FROM orders o1
INNER JOIN orderdetails od1
ON o1.orderNumber = od1.orderNumber
INNER JOIN orders o2
ON o2.customerNumber = o1.customerNumber
INNER JOIN orderdetails od2
ON o2.orderNumber = od2.orderNumber
WHERE od1.productCode = 'S18_1589'
AND od2.productCode = 'S24_1628'
GROUP BY o1.customerNumber;

Describing the logic in words, this will find all orders for the first product number, then require there also be orders for the same customerNumber that has ordered the second product number. (This logic is NOT meant to imply the order the optimizer will choose joins. It is only the logic used to construct the query, based on the requirements stated.)

I dropped the ORDER BY because GROUP BY includes an order by as part of what it does. Although, the optimizer likely would already know it is ordered, and so it is just syntax difference.

As for whether this is more efficient, I would benchmark it to see. For larger data sets I believe my solution will be better than an OR based one, but again, always benchmark.

7
  • The group by is because there are duplicates then of the customernumber due to there being multiple orders per customer. It was either that or distinct. If I try your query, I get duplicates. But if I group by or distinct, I get matching results. Thanks!
    – lg1
    Commented Nov 18, 2018 at 18:47
  • When I actually remove the unnecessary joins and check execution plan - it shows the query cost for mine to be 50.79 vs 138 for yours. I'm not sure how much faith I can put into that but I would have expected it to be better so a little confused. I was trying to get some measure to compare the two queries and that seemed like a good way
    – lg1
    Commented Nov 18, 2018 at 19:26
  • @lg1 That's why I said to benchmark. At the end of the day, that is what matters. And the benchmark will depend heavily on the actual data present. Commented Nov 18, 2018 at 20:22
  • Yeah. I'm trying to replicate a question I was given and was told that what I wrote wasn't considering performance. I didn't get details as to how I could have improved it so trying to understand
    – lg1
    Commented Nov 19, 2018 at 3:22
  • @lg1 I've removed the products table from my query, as others rightly pointed out was unnecessary. I also restored the GROUP BY, as it includes an ORDER BY as part of what it does. Not huge changes. Also, I want to point out, since it appears you are new (or your account is at least) that if an answer is helpful, you should upvote it, and if any of them answers your question you should accept that answer. That both helps other people who find your question in the future see what answers are valuable, and it awards internet points to the people helping you. Commented Nov 19, 2018 at 15:27
0
  1. 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.

  2. 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);
3
  • You're right on the extra join. I forgot to remove it when I was using it to get the proper name of the product. Thanks! As for the query, yeah haven't really used CTE queries all that much. I thought I installed MySQL 8 but I get an error when I try the query 'WITH is not in a valid position`
    – lg1
    Commented Nov 18, 2018 at 18:52
  • If you provide DDL, some sample data, and desired results, we can check instead of guessing... it seems that EXCEPT is not supported by MySQL as well, so i'll add another solution to the answer.
    – SQLRaptor
    Commented Nov 18, 2018 at 19:58
  • The link I gave has a link to the sql that generates all the tables with data. This is the zip file... mysqltutorial.org/wp-content/uploads/2018/03/…
    – lg1
    Commented Nov 19, 2018 at 3:20
0

You can count the distinct number of products per customer among these two products:

SELECT o.customerNumber
FROM orders o
JOIN orderdetails od
    ON o.orderNumber = od.orderNumber
WHERE od.productCode in ('S24_1628', 'S18_1589')
GROUP BY o.customerNumber
HAVING COUNT(DISTINCT od.productCode) = 2
7
  • od needs INDEX(productCode). Is the PRIMARY KEY of o (orderNumber)?
    – Rick James
    Commented Nov 20, 2018 at 3:25
  • A composite index with for example productcode and orderNumber is likely the best choice for an index. It does not matter for the result if ordernumber is primary key or not (for this query). Commented Nov 20, 2018 at 7:58
  • Thank you! I have a question. This is close to the query cost as my original query but still a little worse (~50 vs ~70) - could this be because the number of rows is very small...if the number of rows was substantially more then this query would perform better?
    – lg1
    Commented Nov 20, 2018 at 14:57
  • @Lennart - Yes INDEX(productCode, orderNumber), in this order, would be better because it would both help with the WHERE and be covering (for od).
    – Rick James
    Commented Nov 20, 2018 at 16:03
  • @lg1 - Where do "50" and "70" come from? Yes, larger tables would show more benefit.
    – Rick James
    Commented Nov 20, 2018 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.