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
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 to share