CODEWITHSUNDEEP
phpmysql
user828061
user828061

Reputation: 91

mysql query to fetch all products with no orders for a user

I have a Products table and an Orders table defined in such a way that I can do JOIN query as the following to return Products with zero orders for a specific user.

This query works but its very slow.

select * from products where id not in (select product_id from orders where user_id = 1)

The question is, how to write same query better way and faster?

Upvotes: 1

Views: 203

Answers (3)

Juan
Juan

Reputation: 3705

No need of a subquery for that:

SELECT p.product_id
FROM
   Products p 
   LEFT JOIN Order o ON p.product_id = o.product_id AND o.user_id = @UserId
WHERE
   o.order_id IS NULL -- or any other field that cannot be null on Order

EDIT: for increased performance you may want to check as well that you have indexes in place on the Order user_id column and on your ids (more likely you have them there and probably clustered indexes, both worth to check)

Upvotes: 1

Pit
Pit

Reputation: 3

SELECT P.*
FROM products P
LEFT JOIN (SELECT product_id from orders where user_id = 1) O
ON P.id = O.product_id
WHERE O.product_id IS NULL

Upvotes: 0

vhu
vhu

Reputation: 12798

You should be able to do simple LEFT JOIN

SELET * FROM products 
 LEFT JOIN orders ON (orders.product_id=products.id and orders.user_id=1)
 WHERE orders.id IS NULL;

Upvotes: 1

Related Questions