how to return a null row using union in MariaDB-CodePudding

I have 3 data to be searched in the database.

I need the 3 results to be visible, and the one that has made return null in the query.

I'm using union all to perform the duplicate search, this works.

The question is. I need to get all the data from the survey, even though it's null.

Could anyone get help?

thank you very much for your attention

select * from estoques where `si_id` = '02.352' union all select * from estoques where `si_id` = '05.173' union all select * from estoques where `si_id` = '02.305' order by `validade` asc

In this example the id 05.173 does not exist in the database.

Expected outcome

[
{siaId:'02.352', ...},
{null},
{siaId:'02.305', ...}
]

CodePudding user response：

You can use a left join against a derived table:

SELECT b.*
FROM (
    SELECT '02.352' AS si_id
    UNION ALL
    SELECT ...
) AS a
LEFT JOIN estoques AS b
    USING (si_id);

You may want to include the id in the output to be able to identify missing rows:

SELECT a.si_id, b.*
FROM ...

CodePudding user response：

expand your query to include null where missing/not exists

SELECT * FROM T WHERE si_id IN ('02.305','02.352','05.173')
UNION ALL
SELECT NULL,'02.305',NULL,NULL WHERE NOT EXISTS(SELECT 1 FROM T WHERE si_id = '02.305')
UNION ALL
SELECT NULL,'02.352',NULL,NULL WHERE NOT EXISTS(SELECT 1 FROM T WHERE si_id = '02.352')
UNION ALL
SELECT NULL,'05.173',NULL,NULL WHERE NOT EXISTS(SELECT 1 FROM T WHERE si_id = '05.173')
ORDER BY si_id,DATE

Clearly this does not maintain the order of your desired result so you may need to add a column to sort on.