Video Signals, Cabling
There are five main analog video signal types to be aware of when connecting something to a CRT.
RF will work on pretty much any TV (or VCR with a tuner), but it provides the worst picture. All aspects of the signal go through one cable, leading to a lot of distortion and interference. Using a quality cabling is critical to reduce noise in the image, and a direct connect adapter is generally preferable to a switch-box that may have been provided along with a console. This is generally the least preferable option except in the extreme corner case in a few early games where the developer coded specifically with RF in mind.
Composite was the standard for a long time. This is the simple Yellow-White-Red connector. The yellow head carries video, while the white and red heads carry stereo audio. This connector is the most common and easiest to connect, but the picture quality is still just okay at best.
S-Video is a less common connector that aims to improve picture quality by separating the video signal into two channels within the wire. The yellow connector is replaced with a larger black connector. Not every CRT supports it, but it is said to provide the single largest jump in picture quality.
Component further improves picture quality by separating the video signal into three different components — Y, Pb, and Pr. This is the Green-Blue-Red-White-Red connector. The green head carries the sync signal (Y), the blue head carries the blue color information (Pb), the red head carries red color information (Pr). The green color information is technically missing but can be inferred. The remaining white and red heads still carry stereo audio. Sometimes the audio heads are missing and a different cable needs to be used to get audio.The difference in quality between Component to RGB is technical, not perceivable. Though Component uses math to get the green color information, the end result (given a properly calibrated display) is the same as with RGB.
RGB also separates the signal into three components, but instead of converting to YPbPr, it uses the RGB signals directly. RGB for standard definition displays is generally carried through a SCART cable (a European standard, and it's Japanese counterpart JP21) or BNC connectors (usually only found on professional equipment). Sync for RGB is carried either along the green cable (In the case of RGsB or 'SOG' - Sync On Green), or more commonly carried separately with a combined Horizontal and Vertical sync (RGSB) or further separated into both Horizontal and Vertical sync (RGBHV).
RF, Composite, and S-Video only have enough bandwidth to transmit 240p and 480i (576i in PAL regions) signals. Component and RGB can transmit higher resolutions.
Not every device can output every signal type. Likewise, not every CRT can accept every signal type or display every resolution. Many are "standard definition" only, meaning they can only accept 240p and 480i/576i. Some professional CRTs can handle up to 1080i, but they are few and far between. It is important to know which signals your device can output and which signals and resolutions your CRT can accept before purchasing cables.
When choosing which to use, it is best to balance convenience and quality. RGB technically provides the best picture, but it is not always the most accessible. For example, people in the USA will find Component is much easier to use. In addition, some people prefer the look of RF or Composite because it's what they grew up with. No one signal type is objectively the best.