If you set it up right and pick your TV appropriately, you can get surprisingly good results out of a TV. The biggest issue is often that you'll have to futz around in a weird "service mode" interface that's poorly documented (if at all) to adjust the picture geometry (size and position). If you're using a PC for emulation, you can, to some degree, play with this on the signal source side without affecting the actual imaged area or refresh rate at all.
RGB SCART interfaces are generally the most direct and "best" but, as pointed out, uncommon in the USA. YPbPr component is, in theory, just as good as RGB, but YMMV depending on how you generate it. The older video cards with "HDTV component output" can be quite serviceable for this and generate a superb picture with full control over video timing/resolution. A pure "colorspace transcoder" is also a good option. The digital ones will often do 4:2:2 chroma downsampling, which is undesirable, but you may be able to find one that doesn't. Fully analog designs are also possible. With a good TV, the results should be indistinguishable from RGB if there's no chroma downsampling.
S-Video can even be surprisingly good. The output you get from a PC is junk - it'll always be poorly scaled and with very low color resolution. However, I've had quite good luck using an outboard converter based on e.g. the AD725 chip to convert from RGB. Be sure to find a device that does not scale or anything. Results depend mostly on the TV, but range from "almost as good as a component format" to "utter junk" depending on several factors. Of course, this is only doable at 15kHz region (240p/480i) timings. It won't really work well above that.
Composite is generally crap, but quality varies highly with both how you generate the signal and how good of a TV you use. In general, if you're picking a TV just for this purpose, avoid one that will force you to use composite video.
The RF (antenna/cable) input will generally give results comparable to composite. It won't be any better, but done right, it shouldn't be much worse. Of course, you'll have to use an RF modulator to hook it up to a PC which may add its own problems.
Be aware that many newer CRT TVs, especially those capable of 1080i but also some that can do 480p, include digital scalers that you cannot bypass. These often introduce lag as well as nasty scaling artifacts. They are generally not suitable for this purpose. You'd get better results just using a computer monitor. There are some rare gems that are actually multisync 15/31kHz, so you can feed them either 240p/480i or 480p, and they'll display it all natively. These are great for this purpose. Sets that can go higher without scaling low res modes up seem rare but are probably out there. If you want higher res, you should look into a presentation monitor or some of the later multisync arcade monitors.