There are 4 basic types of video for the Neo CD and here they are from worst to best: RF, composite, S-Video, and RGB.
I've only used composite and S-Video for my Neo CD though, so that's the comparison I'll make.
In general though, RF gives you the worst picture quality and only mono sound. The video and sound signals all go through that single RF connection, and in general, most agree it isn't a pretty sight (or sound).
With all of the other connections, the video and audio are output separately. (There's a video plug, and the red and white audio plugs.)
RGB is the best quality video connection for Neo Systems. What you see in the arcade are RGB monitors. RGB is very nice, but from what I understand, you specifically need an RGB monitor (different from a regular TV) to use it. The RGB monitors I've seen are pretty expensive, and it's hard to find an RGB cable. If you really want to spend the money on an RGB monitor and find an RGB cable, go for it. To me, it's more hassle than it's worth.
Now I'll go into what I've personally used on my Neo CD.
Composite video comes through a yellow plug that has a single metal pin as its connector. Composite video is good, but the picture isn't quite as sharp and there's a little bit of color bleeding compared to S-Video.
S-Video's connector has 4 small pins. It provides a sharper picture and less color bleeding than composite. If you were to see composite and S-Video side-by-side, you could tell the difference, but I personally don't think the difference is Earth-shattering.
Just make sure your TV has an S-Video input. If it does, all you need to do is buy a standard S-Video cable, and you're set. If your TV doesn't have S-Video, I wouldn't go out and buy a new one just for that. If I were just buying a new TV for whatever reason, I'd make sure it does have S-Video though. I like my S-Video and don't want to go back. ^_^