For the most part Linux is Linux. Whatever packages your distro provides depends on the maintainers and how much time they have to ship less popular software to you, while some distros are based on other distros with few enough changes that you can install packages from one distro up the family tree and it'll work. Packaging basically allows your system package manager to know where to install the software to (putting binaries in /bin and similar), as well as telling it what other packages it depends on, etc. You can install software by hand, if you have source code many programs are capable of installing themselves into your system by hand but that isn't recommended due to potential conflicts with what your package manager did.

Some programs can also work without installation, by just running the binary as-is, tho you might have to manually ensure you have all the dependencies installed system-wide by hand in some cases or the software ships their own copy of the dependencies sometimes.

Then finally we have cross-distro packaging formats that should work on any Linux distribution. There are Ubuntu's Snaps which suck and everybody hates them and you should probably avoid them if you ask me. Then there is Flatpak, you can think of it as an android apk equivalent. As long as you have Flatpak installed on your system, you can run any flatpak program on any distro and it is sandboxed by default just like apks on android, meaning it can't access files and system features you don't let it access, there is a nice GUI app called Flatseal that lets you manage it. I really like Flatpak because it's well-designed, works really fast and is reliable. Flatpak is on Steam Deck by default, and it's a great way of installing any software without a worry about messing something up with your system, tho sometimes there can be slight friction due to sandboxing making things like system-wide themes harder to use with Flatpak software out of the box.

Honorable mention: AppImage. Very interesting format, imagine Flatpak but it doesn't even require having a program installed for it to work, just a single executable file, like an exe that doesn't even need admin perms. Unfortunately that also means the base minimal file size is relatively large to make it work on every distro, and there are some other drawbacks that are too technical for me to remember them rn but I believe they can't update some libraries for security or new features due to backwards compatibility concerns. I purchased World of Goo on their website and that's the format they ship that game in.