It can definitely be misused, but SELECT * is pretty handy for ad-hoc queries and to succinctly get all (or almost all) of the columns for a subquery or CTE.