SD cards are smaller, slightly more expensive for equivalent storage (although this gap is shrinking fast), and access is somewhat slower. This can be a problem because sometimes movies have trouble playing from SD cards if the bitrate is too high. On the Zaurus, you can only use the SD slot for flash storage cards. The driver for the SD slot is binary only, which can be a problem if you want to experiment with alternate kernels.
CF cards are bigger, slightly cheaper per megabyte, and go up to much larger capacities. (I think the current max is 4GB, but it increases every year). You can even buy \"microdrives\" which are basically a standard hard drive shrunk down into a tiny package. CF is based on PCMCIA, which is a venerable technology invented for laptop expansion a very long time ago. The CF slot is useful for many more things besides storage - you can put all kinds of devices in there, network/wireless cards are just one common example.
Because the CF slot is so much more flexible, most people use the SD slot for holding a memory card with all the programs and data, and use the CF slot for other things like network access.