That varies from game to game. From my anecdotal experience, NES games look better in pixel perfect modes, since the developers didn't/couldn't take the actual TV output into account while developing these games, so they just developed for a 256×224 picture. However, slightly more recent consoles such as the SNES, PS1 or N64 actively worked with the predicted output to create their images. For example, Donkey Kong Country on the SNES was developed with an anamorphic (stretched) 4:3 aspect ratio in mind, despite having the same 8:7 internal resolution, and they fully took into account that the picture would have scanlines, so it renders expecting an illusion of continuity in those black lines. Playing it in pixel-perfect mode doubles/triples every line, displaying a blocky image where your brain would perhaps automatically fill in a diagonal line or rounded picture, as well as forcing the "correct" aspect ratio that developers actively worked around (like playing an anamorphic widescreen DVD in 4:3 just because "it's still 640×480", assuming you live in an NTSC region). While the N64 and the PS1 actually render in 4:3 (320×240), they still expect scanlines, and the N64's anti-aliasing is completely broken if you force line-doubling. And the continuity illusion I mentioned is even more important in 3D rendering.
TL; DR: It's different on a case-by-case scenario. As a rule of thumb, NES looks just fine in pixel-perfect mode/nearest-neighbor scaling. Your mileage may vary with SNES games. Mega Drive/Genesis, Saturn, N64 and PS1 definitely look a lot better on a CRT or with a proper filter ("Sharp Bilinear" with a 640×480 — "Original" — window in Adrenaline for PSVita is a good example)