Why does joining meshes result in a joined mesh with (previously) non-actively selected meshes being transparent?
I figured it out!

The issue: 
The UV maps didn't get combined on join because they did not have the same name. This resulted in all but the first UV map becoming inactive and all but the actively selected mesh, while having the correct material assigned, using that first UV map. That then resulted in the assigned materials then not being mapped properly.

Solution:
Before joining the meshes, rename all UV maps to the same name. This can be done in "Object Data Properties" (the green triangle above the material tab) > "UV Maps" --> Double-click on names to rename. Then simply join as usual. The UV maps will now be combined correctly and the corrected parts of the resulting mesh will have the correct materials assigned to them properly.