Seems like it should definitely not be the default sorting mechanism. It's a fringe use case at best. Most people subscribe to communities across the 'verse and their default should be "subscribed" and have 5> communities and "all" if not logged in or have fewer than 5 community subscriptions.
It's a feature of federation. Each instance can have it's own communities, even with the same name. So there could be hundreds of "no stupid questions" communities out there.
Picking 'local' let's you see the feed of your instance. Picking 'all' shows you the feed from all.
On a well-fedorated general purpose instance, it likely wouldn't matter, like lemmy.world. however, it would be useful if
Your instance is dedicated to a specific purpose, like lemmyNSFW and some programming/art/starterk oriented instances. Then you can view all the instance content without going into specific communities.
You instance has special moderation rule. You might want to take a break from wider internet, and only enjoy well-moderated content on your instance.
It means only the sexy hot singles 5mi from you can post. Click here to meet up with sexy girls who wanna bang! (sexy girls who want to bang are not near you)
EDIT: oh, sorry for joking, right, let me repeat the answer that everyone else has repeated now 20 times over...
I wanna add to this that "all" isn't really from all Lemmy instances, but instead only from communities your instance knows about.
I'm not sure if anyone on your instance has to be subbed to that community or if it's enough if that instance was searched for once on your instance.
It only shows communities on the same instance as you. So you're resisted on lemmy.world so if you click "Local" it will only show you communities also hosted on lemmy.world