Mysql query returns nothing when subquery returns 0 rows

When trying to select fields from a subquery, if ANY of the subqueries returns no rows, nothing is returned (not even an empty result set)

SELECT sub1.field, sub2.another_field
    FROM (
        (
            SELECT field
            FROM table
            WHERE id=1
        ) AS sub1, (
            SELECT another_field
            FROM table
            WHERE id=2
        ) AS sub2
    )

      

I was hoping for a string containing a value NULL

when the subquery returns 0 rows.

+3


source to share


2 answers


You need to do outer join like this



SELECT sub1.field, sub2.another_field
FROM (
        SELECT field, id
        FROM tab1
        WHERE id=1
    ) AS sub1 LEFT OUTER JOIN (
        SELECT another_field, id
        FROM tab1
        WHERE id=2
    ) AS sub2
  ON sub1.id = 1 and sub2.id = 2

      

+3


source


To solve the problem of a query returning nothing when all subqueries return null rows, I wrapped the whole batch in UNION

with an empty string.



SELECT * FROM (
    SELECT sub1.field, sub2.another_field
    FROM (
        SELECT field, id
        FROM tab1
        WHERE id=1
    ) AS sub1

    LEFT OUTER JOIN (
        SELECT another_field, id
        FROM tab1
        WHERE id=2
    ) AS sub2
    ON sub1.id = 1 and sub2.id = 2

    UNION

    SELECT NULL, NULL

) AS t0

      

0


source







All Articles