CODEWITHSUNDEEP
sqlwhere-clausehaving
friskybunnies
friskybunnies

Reputation: 21

SQL Query: How to return products that don't have reviews

I have a table of reviews and a table of products. E.g. the following dummy data:

enter image description here

enter image description here

I want to return only the products that do not have any reviews.

My SQL code is currently

SELECT *
FROM products
LEFT JOIN reviews
ON products.id = reviews.product_id
WHERE reviews.content IS NULL;

As each product has multiple reviews, only some of which are null, I'm concerned that this approach returns false negatives (i.e. I don't want products that are associated with some empty reviews among other completed reviews, I only want products for which no reviews exist at all). Do I need to add a HAVING clause?

Thanks much for your help in advance.

Upvotes: 1

Views: 327

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Just use the join key for the comparison -- not an arbitrary column:

SELECT p.*
FROM products p LEFT JOIN
     reviews r
     ON p.id = r.product_id
WHERE r.product_id IS NULL;

Or more colloquially perhaps with NOT EXISTS:

SELECT p.*
FROM products p
WHERE NOT EXISTS (SELECT 1
                  FROM reviews r
                  WHERE p.id = r.product_id
                 );

Upvotes: 1

Related Questions