How to return rows in order including nulls in MariaDB-CodePudding

How to return rows in order including nulls in MariaDB.

I have a list of ids that I want to query, but the actual result is not what I expected.

The record 05.173 does not exist, but I need the null line to be in order.

Is this possible?

 SELECT b.*, a.sia_id
    FROM (
        SELECT '02.352' AS sia_id
        UNION ALL
        SELECT '05.173' AS sia_id
        UNION ALL
        SELECT '02.352' AS sia_id
        UNION ALL
        SELECT '05.173' AS sia_id
        UNION ALL
        SELECT '05.451' AS sia_id
    ) AS a
    LEFT JOIN estoques AS b
        USING (sia_id) order by `validade` asc

Current results

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

expected outcome

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

CodePudding user response：

You can add an ordering attribute to your derived table:

SELECT b.*, a.sia_id
FROM (
    SELECT '02.352' AS si_id, 1 as oa
    UNION ALL
    SELECT '05.173' AS si_id, 2
    UNION ALL
    SELECT '02.352' AS si_id, 3
    UNION ALL
    SELECT '05.173' AS sia_id, 4
    UNION ALL
    SELECT '05.451' AS sia_id, 5
) AS a
LEFT JOIN estoques AS b
    USING (sia_id) 
order by a.oa

Any order in a result set without an explicit order by, should be considered as a coincidence.