How to Retrieve All Categories and Their Minimum/Maximum Product Prices in MySQL, Even When Some Categories Have No Products?

Selecting from Two Tables with Unequal Rows in MySQL

In an online shopping database with two tables - categories and products - a common task is to retrieve all categories along with the minimum and maximum product prices in each category. However, a straightforward query encounters an issue: categories without products are excluded from the results.

To address this challenge, it's necessary to use an outer join instead of an implicit join. An outer join allows rows from one table (in this case, categories) to be retrieved even if there are no corresponding rows in the other table (products). Specifically, a left join is used here, so that all categories are included in the results.

Additionally, to replace null values with 0 for categories with no products, the IFNULL() function is employed. This function takes the value of the expression provided in the first argument, and if it's null, returns the value specified in the second argument instead.

The updated query syntax is as follows:

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

By using an outer join and the IFNULL() function, we can retrieve all categories and ensure that categories with no products are represented with minp and maxp values of 0. This query effectively handles the need to select from two tables even when not all rows in one table have corresponding entries in the other.

The above is the detailed content of How to Retrieve All Categories and Their Minimum/Maximum Product Prices in MySQL, Even When Some Categories Have No Products?. For more information, please follow other related articles on the PHP Chinese website!