Depends on the quality of the infrastructure. I live in a metropolitan area with elevated metros, suburban and long distance trains, cargo trains, trams (streetcars in american), etc. and it vastly differs. Paris line 6 makes a lot of noise due to the rubber tires and frequent turns (it's a semicircle line), meanwhile modern trams and modernised suburban rail (RER A) are barely audible even next to the tracks, let alone in buildings nearby.
I lived next to a freight line, never again. The corner was in my backyard. My comment is mostly about daily life outside of the house, and in a city center. So those moments on foot when you're out of the car.
For life in the city, it's much more pleasant to be in a foot only or mixed traffic street, even with a train nearby. There's the inherent danger of fast cars operated by non-professionals that you perceive on foot as well as the noise, neither you really need to worry about for trains. In many cities trams can run through pedestrian areas with no barricades or grade separation because they are slower and predictable. You can run cars with no separation too if you slow them down enough, but more often they are set to incompatible speeds and as a pedestrian you have all the downsides of the cars as they pass through what should really be a pedestrian space in most city centers.
It's a different kind of sound. Tire noise is white noise (or at least, similar). My experience working in close proximity to a light rail line was 1) the train shakes the ground as it goes by, and 2) if you're near a corner, the squeaking is pretty loud. We have water sprayers in those areas to try and cut down on the howling, but it's not a panacea.
That's just bad design. There very quiet tram and railway designs available - they just cost a bit more money than the cheap 19th century designs that are still in place.
Where I live in Germany the passenger trains (not even the tracks) got upgraded a few years ago and all those click-clack and screeching sounds are gone. What is left is a wooshing sound of the wind being pushed aside and the not-so-loud grinding sound of the thingy (collector?) that hits the power cable.
And I've lived across from from an elevated transit line in an otherwise carless city center and it was quieter than any north american subwayless city I've lived in so this isn't even true lol.
https://news.ycombinator.com/newsguidelines.html