Why does the outer query return nothing if second subquery returns no results?-CodePudding

I'm trying to figure out why the outer query returns nothing if the second sub query has no return value even if the first does?

SELECT * FROM
(
    SELECT category_id
    FROM story_category
    WHERE story_category.story_id = 9998
) AS c,
(
    SELECT typology_id
    FROM story_typology
    WHERE story_typology.story_id = 9998
) AS t;

If I pick another story_id for the second query which returns a typology_id only then does the main query return a results for both subquery:

CodePudding user response：

Having two elements in the FROM list means a Cartesian product or cross join, that is, each row from the left side is combined with each row from the right side. If the left side has n rows and the right side has m rows, the result will have n*m rows.

Consequently, if one of the sets is empty, the result is also empty.

CodePudding user response：

I think you need JOIN

SELECT category_id, typology_id
        FROM story_category sc
        JOIN story_typology st on sc.story_id = st.story_id
        WHERE  sc.story_id = 9998;