MySQL order by NULL values

Von in php

Ever won­der how to get NULL results of a MyS­QL que­ry sor­ted to the end of the table, while more usable and avail­ab­le data gets sor­ted to the top? Ima­gi­ne the fol­lo­wing query:

SELECT * 
FROM first_table ft
LEFT JOIN second_table st ON ft.id = st.fk_id AND st.deleted != 1
ORDER BY first_value ASC

Becau­se of the con­di­ti­on “st.deleted != 1” wit­hin the JOIN some results might get remo­ved from the out­put for the second_​table, lea­ving behind an empty space, respec­tively some NULLs. The sorting “ORDER BY first_​value” will still be working fine, but rows with the NULL value might occur bet­ween valu­able rows. May­be tho­se NULL rows can’t be remo­ve from the result for rea­sons, but it would be more plea­sant to sort them to the bot­tom of the table, whe­re they do not pol­lu­te the more valu­able out­co­me? Don’t do this with PHP or Java­Script. Do it wit­hin the query:

SELECT * 
FROM first_table ft
LEFT JOIN second_table st ON ft.id = st.fk_id AND st.deleted != 1
ORDER BY ISNULL(first_value), first_value ASC

The­re are two pos­si­bi­li­ties: ISNULL(first_value) ASC puts rows with NULL values to the bot­tom, ISNULL(first_value) DESC puts them to the top of the table. But it’s also pos­si­ble to chan­ge the sorting by nega­ti­on: !ISNULL(first_value).

Share on LinkedInShare on Redditshare on TumblrShare on StumbleUponDigg thisShare on FacebookGoogle+Tweet about this on TwitterEmail to someone