Cacher is the code snippet organizer for pro developers

-- example of how to sort NULLs at the end of a list. In this case, the comm column contains NULLs that you want to sort at the bottom. To do this, a SELECT subquery is used to compute a column called value_is_null - this columns uses a CASE statement to assign a value of 0 if comm is NULL, or 1 if comm is NOT NULL. The final result set is then sorted descending by value_is_null (even though this column isn't SELECTed and displayed), which places the NULLs at the bottom. This is a good example of using a SELECT subquery to return data that isn't in the original table, and this data can be used to order or otherwise manipulate the final result set returned (even if this manipulation data isn't displayed in the end)
SELECT ename,sal,comm 
FROM (
SELECT ename,sal,comm,
CASE 
  WHEN comm IS NULL THEN 0 ELSE 1
END AS value_is_null
FROM emp
) x
ORDER BY value_is_null DESC,comm;

------------
-- DETAIL --
------------

-- SELECTs final result set from the subquery
SELECT ename,sal,comm 
-- SELECTs FROM the subquery
FROM (
-- SELECT ename, sal, and comm columns from the subquery
SELECT ename,sal,comm,
-- SELECT a CASE statement - if comm is NULL, assign value of 0, else assign a value of 1
CASE WHEN comm IS NULL THEN 0 ELSE 1
-- END and name the column value_is_null
END AS value_is_null
FROM emp
-- give the subquery an alias
) x
-- ORDER BY value_is_null (even though value_is_null is not SELECTed and therefore won't be visible in the final result set)
ORDER BY value_is_null DESC,comm;

/*Results with NULLs listed at the bottom:

ename	sal	comm
TURNER	1500	0
ALLEN	1600	300
WARD	1250	500
MARTIN	1250	1400
BLAKE	2850	NULL
CLARK	2450	NULL
SCOTT	3000	NULL
KING	5000	NULL
ADAMS	1100	NULL
JAMES	950	NULL
FORD	3000	NULL
MILLER	130	NULL
JONES	2975	NULL
SMITH	800	NULL

*/