tl;dr: Doing something radical like using a "burner" PC or device that you will use one time to read the USB stick and then discard is an (almost) completely bulletproof way of seeing what's on the stick. But actually going to such extremes while investigating is overkill and a little silly. Except where it isn't.
Believe it or not, there is a nearly foolproof way to examine such a USB stick. Step-by-step:
Find some super-old, super-cheap, but-still-somehow functioning laptop/netbook on the Internet and buy it. (Any tablet large enough to have a full-sized USB port and with an OS that can use external storage on that USB port works also.)
Alternative #1: If, however, you also really care about not potentially infecting the USB stick via plugging it into some previously owned device of unknown security history you could just as well for, say, a $60-$70-ish bottom-of-the-barrel new Windows tablet with a full USB port. (They aren't hard to find on Newegg, Amazon, eBay, etc. and via sites like Dealnews.) Cheapest-of-the-cheap commodity hardware has its place.
Alternative #2: If you want to save a little cash and you already have an old, crappy, or old & crappy device you'd be happy sacrifice for the purpose of finding out what's on that USB stick you can certainly go that route instead. However, pretty obviously you'd want to make sure that there would be absolutely, positively no personal (or professional data) left on it of any kind before doing so. With a PC that has a classic hard drive you can very likely accomplish that by wiping it with a boot program that overwrites every bit of space on the disk with random data many times over, and then re-installing whatever OS you want. Probably. On the other hand, if you want to use a device that has solid-state storage....
When the package containing your device arrives, grab it, an appropriate charging cable that you're willing to sacrifice (you'll see why in a minute) and make a trip to a location that has power plugs but either (a) no wireless network availability or (b) at least no wireless networks that you've ever connected to before and in all likelihood will never connect to in the future. (A Panera or Starbucks on the other side of town that's far out of your nomal way works great). Just to cover the hypothetical case where some super-ultra sophisticated NSA-level malware present on the USB stick infects your device and then autonomously starts using its radios to try to breach any Wi-Fi, Bluetooth, etc. networks around it. Paranoia bonus: Also leave all other electronic devices of yours that have any kind of wireless connectivity at home. (Yes, including your smartphone. I know it's hard to be apart, but just this once.)
When you arrive at your location, unbox and plug-in your new device. Wait for it to charge a bit.
Turn on your device, wait for it to boot, and plug-in your suspect USB drive. Have a look at anything that's on it, its file structure, whatever characteristics you like. If you are in a place that does have public wifi, maybe connect and grab some tools from the Internet (if your old piece-of-junk will install & run them) and take a closer look. Do literally nothing else with the device.
When you have satisfied your curiosity, grab your device and your charger, go out to a field somewhere nearby, and give them a nice final sendoff by re-enacting that scene from Officespace. (Alert: Auto-playing YouTube vid, with probably NSFW language. Duh.)
Do whatever you've decided to do with the USB stick & any data on it.
(Okay, if you pride yourself with not being hugely wasteful and/or environmentally irresponsible, instead of destroying your "burned" device/PC in a fun manner you could recycle it, donate it to charity, or sell it for a pittance. If you go either of the latter two routes, should you tell the receiving party exactly why you're getting rid of the device? Well, let's maybe call that a cybermorality question for another day.)
The End.
Well, okay, I'm being a little facetitious. But only somewhat. The fact remains that if we're talking about examining a USB device with (nearly) zero security risk the only real option is to plug it into a system that (a) contains absolutely no sensitive info of yours, (b) you are willing to sacrifice should the USB turn out to be some electrically-malicious item, (c) you will never use again for any purpose that requires putting any kind of trust in its security, and (d) will not physically be able to connect to any networks or other devices to spread any malware infection it might get from the questionable USB drive. (Or to seek out any sensitive info that might reside on those devices and/or networks.)
In other words, a "burner" computer is your best bet. If you really, really, really want to examine the drive with almost* perfect safety/security, that is.
Now, if we're just talking about examining the USB stick with a "very likely good enough, given practical considerations" degree of safety/security, @Chris H's suggestion above is a good one: grab a desktop PC or a laptop machine (that you can actually open/service without professional tools), take out the storage drive/s, boot from a live CD/USB OS flavor you prefer, and plug in the suspicious/intriguing USB stick. Is there still a small chance that the USB could contain sophisticated malware that could execute when you plug the USB stick in and then flash your machine's BIOS/UEFI, or flash other writable firmware contained in things like your video card, your networking card, your USB controllers, etc.? Yes. (Although right now all the stuff besides BIOS/UEFI attacks remain very rare in the wild. And even BIOS or UEFI malware needs to be written specifically for the maker/version implementation used in a targeted machine.) Could the item that appears to be a USB memory stick in actuality be a USB-killer that will electricallyfry your motherboard? Well...theoretically, yes. But the probabilities against either of those things being true--especially the USB-killer one--are strongly in your favor. To paraphrase a good point you made in your question, most of the time a plain old USB stick is just a plain old USB stick.
Unless you, your employer, on another entity you are a part of could be considered a very high value target by some sophisticated attacker out there, that is. Then all bets are off. And in which case, a convoluted safety-above-all-else method like the one above might actually be the only appropriate one.
*Of course, there's no such thing as "perfect" security. But "almost perfect" security is close enough for our purposes here.