Products Lacking Reviews
Write a PostgreSQL query to list all products (product ID and name) that have not received any customer reviews. Sort the results by product ID.
Related Concepts
Hint
There are several ways to approach this. Consider these common patterns:
LEFT JOINwith aNULLcheck:LEFT JOINallProductstoReviews. Products that have no reviews will haveNULLvalues for the columns from theReviewstable. Filter for these.NOT INwith a subquery: Select products whoseproduct_idis not in the set ofproduct_ids found in theReviewstable.NOT EXISTSwith a correlated subquery: For each product, check if there isn't any corresponding review in theReviewstable.
The LEFT JOIN approach is often very readable and performant for this type of problem.
Solution (PostgreSQL)
Imagine you have two lists: one with all your Apple products, and another with all the customer reviews received.
To find products without reviews, you're essentially looking for products on your main product list that don't show up at all on the reviews list.
One way to do this in SQL is to try and match every product with its reviews. If a product doesn't find any matching reviews, it means it has none! We then just pick out those "unmatched" products.
PostgreSQL Query (using LEFT JOIN)
-- Find products that have not received any reviews
SELECT
p.product_id,
p.product_name
FROM
Products p
LEFT JOIN
Reviews r ON p.product_id = r.product_id
WHERE
r.review_id IS NULL -- The key: no matching review means r.review_id (or any r column) will be NULL
ORDER BY
p.product_id ASC;
Explanation of the PostgreSQL Query:
SELECT p.product_id, p.product_name:
- Specifies the columns to be returned from the
Productstable (aliased asp).
FROM Products p LEFT JOIN Reviews r ON p.product_id = r.product_id:
FROM Products p: Starts by selecting all rows from theProductstable.LEFT JOIN Reviews r ON p.product_id = r.product_id: This is the crucial part.- A
LEFT JOINincludes all rows from the "left" table (Productsin this case) and the matched rows from the "right" table (Reviews). - If there is no matching row in the
Reviewstable for a givenproduct_idfrom theProductstable, the columns from theReviewstable (liker.review_id,r.user_id, etc.) will be filled withNULLvalues for that product.
- A
WHERE r.review_id IS NULL:
- This condition filters the results from the
LEFT JOIN. - It selects only those rows where
r.review_id(or any other column from theReviewstable that would be non-null if a match existed, like its primary key) isNULL. - This effectively identifies products from the
Productstable that had no corresponding entry in theReviewstable.
ORDER BY p.product_id ASC:
- Sorts the final list of products without reviews by their
product_idin ascending order, as per the requirement.
Alternative Methods:
1. Using NOT IN with a Subquery:
SELECT product_id, product_name
FROM Products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM Reviews WHERE product_id IS NOT NULL)
ORDER BY product_id ASC;
(Note: Using DISTINCT and checking for IS NOT NULL in the subquery for NOT IN is good practice to avoid issues if Reviews.product_id could be NULL itself, though unlikely for a foreign key.)
2. Using NOT EXISTS with a Correlated Subquery:
SELECT p.product_id, p.product_name
FROM Products p
WHERE NOT EXISTS (
SELECT 1
FROM Reviews r
WHERE r.product_id = p.product_id
)
ORDER BY p.product_id ASC;
The LEFT JOIN ... IS NULL pattern is often preferred for its clarity and generally good performance across many database systems for this type of "anti-join&" problem.
Your Turn! What are your thoughts on this problem or alternative ways to solve it? Share your own SQL query attempts or insights in the comments below!