mysql left join order by null values to the end

Question

mysql

SELECT * FROM media
LEFT JOIN media_priority
ON (media_priority.media_id = media.id AND media_priority.media_tag = '".$tag."')
WHERE something = 'something'
ORDER BY media_priority.media_order;

This works fine except that media_priority.media_order sometimes comes back as null and mysql puts the null values at the top.

So I was trying to figure out how to do an IS NULL AS tempcol statement with my left join so I can order by that first.....I just can't seem to get the syntax right.

Where would I put the IS NULL statement in my query above?

I was thinking something like:

LEFT JOIN media_priority
ON (media_priority.media_id = media.id AND media_priority.media_tag = '".$tag."')
media_priority.media_order IS NULL AS isnull

but that doesn't work.

Answer 1

I recommend ordering by the boolean value of media_priority.media_order IS NULL first, then the column value, in order to force the NULLs to the end.

Like this:

SELECT * FROM media
LEFT JOIN media_priority
ON (media_priority.media_id = media.id AND media_priority.media_tag = '".$tag."')
WHERE something = 'something'
ORDER BY (media_priority.media_order IS NULL) ASC, media_priority.media_order ASC;

Answer 2

ORDER BY 
    CASE WHEN media_priority.media_order IS NULL THEN 1 ELSE 0 END, 
    media_priority.media_order;

Or use a magic number if you have some upper limit which media_order will never feasibly reach.

ORDER BY 
    COALESCE( media_priority.media_order,99999999);

The first approach is obviously somewhat safer!