Short story:
Composite Video
Pro: Single video cable. Can be used with an RF modulator
Con: Compromises with color and luminance artifacts and reduced luminance and color resolution
S Video
Pro: Can reduce or eliminate color and luminance artifacts. Uses a single DIN connector.
Con: Lower color resolution than component which can have more color smear, but not greater than composite. No RF connectivity.
Component Video
Pro: Can reduce or eliminate color and luminance artifacts. Keeps color signals separate to allow improved resolution.
Con: Three connections are required, so obviously no RF connectivity.
Less short story:
The problem with composite video is separating the chroma and the luminance signals. This can result in crosstalk between the two signals which creates artifacts, and can decrease resolution. Chroma leaking into the luminance can appear as a crawl effect going from bottom to top (NTSC). Luminance leaking into chroma appears as rainbow patterns or flicker on diagonal edges. The NTSC color separation technique has improved over the years. A simple frequency filter was first used (1D), then adaptive line combs became common (2D) and then adaptive frame combs became popular (3D). Each improvement allowed decreased artifacts and better resolution. However, keeping the two separate is still a better approach. In areas that use NTSC, two methods are commonly used for analog video: S Video and component. RGB is another option, but is not very common for consumer video connectivity.
The chroma signal is derived from two color difference signals quadrature modulated on a subcarrier. The resolution of the color signals are usually reduced. S Video uses the luminance and the chroma signal (Y & C), while the component uses the luminance and the two baseband color difference signals separately (Y, Pr, Pb). Both keep the luminance and chroma separate which eliminates the issues of luminance/chroma separation in composite. Component can offer improved chroma resolution over S Video which results in less horizontal chroma smear, but this advantage is more subtle than they both have over composite.