Selecting from Two Tables in MySQL with Missing Correspondents

To retrieve data from two tables in MySQL and handle cases where not all rows in one table have corresponding rows in the other, you can utilize an outer join. This approach differs from the outdated implicit join method and offers greater flexibility.

Consider the following example:

You have two tables, categories (with columns id and title) and products (with columns id, ownerid, title, and price, where ownerid references id in the categories table).

Your query aims to retrieve all categories and their minimum and maximum prices, grouped by category ID. However, the current query excludes categories without corresponding products.

To resolve this issue, employ a LEFT JOIN:

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

The LEFT JOIN ensures that all rows from the categories table are included, even if there are no corresponding rows in the products table. The IFNULL function assigns 0 to minp and maxp for categories without products, preventing NULL values from being displayed.

Alternatively, you may prefer to return NULL for these categories instead of 0, depending on your specific requirements.