B
B
BonBon Slick2021-01-10 08:53:47
SQL query optimization
BonBon Slick, 2021-01-10 08:53:47

SQL query optimization customer never bought?

Used material
First
Second
Third

+ off docks

Here, for anyone with subqueries, I'm wondering how NOT EXISTS, COUNT + DISTINCT, IS NULL will work with this structure

FROM User U
         INNER JOIN `Order` O on O.customer_id = U.id
         INNER JOIN OrderItem OI on O.id = OI.order_id
         INNER JOIN Product P on OI.product_id = P.id
         INNER JOIN Store S on P.store_id = S.id


Here is the current working version without taking into account the resources above. Optimizing it.
SELECT U.name, U.id as customer_id
FROM User U
WHERE U.id NOT IN (SELECT O.customer_id
                          FROM Product P
                                   JOIN OrderItem I on P.id = I.product_id
                                   JOIN `Order` O on I.order_id = O.id
                          WHERE P.store_id = 5)


MariaDB RDBMS on which I tested the query.

Original dump
SQLFiddle

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question