join - How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?

Question

I have two tables for an online shop:

• one for categories: id, title
• one for products: id, ownerid, title, price (ownerid is the id of the parent category)

I want to select all the categories and also select the minimum and maximum price in each, hence the following query:

SELECT
sc.*, MIN(s.price) AS minp, MAX(s.price) AS maxp
FROM
categories AS sc, products AS s
WHERE s.ownerid=sc.id
GROUP BY sc.id

It works pretty much as expected with the only exception that if a category doesn't have any product in it, then it is not selected. While this seems logical since I ask for "s.ownerid=sc.id", I don't know enough SQL to make it work as intended. I need all the categories and for the ones that don't have products minp and maxp should be 0.

Any advice? Thanks.

Answer 1

To do this you need an outer join. By the way, the way you are writing your query with an implicit join is outdated and no longer recommended. Using the JOIN keyword is recommended. This also makes it easier to change an inner join to an outer join.

FROM categories AS sc
LEFT JOIN products AS s
ON s.ownerid=sc.id

To return 0 instead of NULL use IFNULL(..., 0). The entire query becomes:

SELECT
    sc.*,
    IFNULL(MIN(s.price), 0) AS minp,
    IFNULL(MAX(s.price), 0) AS maxp
FROM categories AS sc
LEFT JOIN products AS s
ON s.ownerid = sc.id
GROUP BY sc.id

You may also want to consider if it would be better to return the default NULL instead of 0 for categories that have no products.