Single pointed (straight):
What most people think of when they imagine knitting needles. These are pairs of straight rods, one end each with a rounded point, the other end covered with some sort of permanent cap. These are available in a number of lengths and all sizes.
Double pointed (straight):
Much like single pointed needles, double pointed needles, or dpns, are simply pointed on both ends. These are what people used to use to knit in the round prior to circular needles. These are still preferred by many for making small tubular items such as socks and mittens. They are sold in packs of 4 or 5, in a variety of lengths, usually short, although long ones exist for Shetland lace. To substitute dpns for straight needles, simply block one end each with a wrapped rubber band or a point protector.
A flexible double pointed needle suitable for knitting in the round and flat knitting. Two rigid or semi-rigid pointed tips are joined by a nylon filament or cable. These are available in a wide variety of lengths and most sizes, although extremely large sizes are hard to find and I don’t think anyone makes circulars smaller than a US#1. Skacel makes a 47 inch long US#36. Two circulars of the same size are useful for very wide flat projects. As with dpns, you can block one end each with a point protector or by wrapping it with a rubber band.
These needles are a cross between circular and straight needles. They look like circular needles except that, instead of ending in 2 points, they have 1 point and 1 stopper at the end of the flex or flexible nylon filament. These are often available in long lengths suitable for afghan making, and are more comfortable to use than straight needles for heavy projects. I don’t much see the point of them however, since a circular needle can be used instead, and is more versatile.
This is used in conjunction with regular knitting needles of any variety. Cable needles are double pointed and come in several shapes. Some are U shaped, with one leg longer than the other. Some are like a straight needle with a bend in the middle like this:

___________      ___________
Some are short dpns that narrow to a smaller diameter in the middle. Some are just short dpns, and some people choose to use a regular dpn instead. Cable needles come in a few sizes, generally small, medium, and large. Size is fairly flexible with cable stitch needles, and with care I have used a bent paperclip to good effect.

A cable needle is used to slip stitches and hold them in front or back of the fabric being knitted, while adjacent stitches are knit. Then, the held stitches are knit off of the cable needle. This causes a twist to form, and is very popular in aran and fisherman knit sweaters. One generally only needs 1 cable stitch holder for any given project; although more complex cables may require 2. This may happen if you are ‘braiding’ the twist: such as placing 2 stitches in front, 2 stitches in back, knitting 2, and then knitting off the 2nd stitch holder and then off the first. I have done this for Lily Chin’s reversible cable technique.

These days I don't bother to use a separate needle. Since I'm only ever moving around a few stitches at a time, I put them on a split stitch marker, and then back onto my left needle when it's time to knit them. The benefit of this is that it's much more secure if I have to stop knitting in the middle of a cable, and there's less clutter. It's particularly useful for braids, because of this.

a condo needle is a circular needle with removable heads or tips. There are two US#7 and one US#15 tips (I've seen other size pairings as well, but this is the most common). The instructions off the Susan Bates packaging – Work 1 row with the larger head, then 1 row with the smaller head. As the instructions indicate, every other row one is to switch the tip which receives the new stitches. This creates, as would be expected, alternating loose rows for a decorative effect.
Broomstick lace pin:
sold singly, very large, and not actually for knitting. These are used in conjunction with a crochet hook for creating broomstick or jiffy lace.

Needle materials: steel, coated steel (nickel, teflon-like substances), brass, aluminum, metal alloys, assorted woods, bamboo, nylon, bone (as far as I know, no longer being made), baleen(also, as far as I know, no longer being made), casein, and assorted other synthetics such as cellulose acetate.

Each material handles differently. Nickel plated steel needles, such as Addi Turbos, make for incredibly fast knitting if you are up for it. On the other hand, it is easier to drop a stitch. Wood, bamboo and aluminum are less slippery, which means slower knitting but fewer dropped stitches, especially when using dpns. Wood, bamboo, and synthetics are smooth, light and flexible, and feel warm to the touch. This makes for more comfortable knitting, and many people with arthritic hands swear by casein. If you can, try out a pair before you buy. Over time, how comfortable your needles are will add up.

Needle sizes: Needle sizes are based upon the diameter of the barrel of the needle. All sizes, even metric, are approximate, and sizes between companies differ. Some companies also make ''true'' half sizes (US#10.5 is not a half size, it's a full size different than a 10 and a 11). Knitting gauge is more important than needle size, so feel free to try out different sizes until the gauge is right.

US	 mm	UK     Japanese
00000000 .5     24
000000	 .75    22
00000	 1      19
0000	 1.25   18
000	 1.5    17
00	 1.75   15
0	 2	14 
         2.1              0
1	 2.25	13
         2.4              1
         2.7              2
2	 2.75	12
	 3	11        3
3	 3.25	10 
         3.3              4
4	 3.5	          
         3.6              5
5	 3.75	9
         3.9              6
	 4	8         
         4.2              7
6        4.25
7	 4.5	7         8
         4.8              9
8	 5	6         
         5.1              10
         5.4              11
9	 5.5	5         
         5.7              12
10	 6	4         13
         6.3              14
10.5	 6.5	3
         6.6              15
	 7	2         7mm
	 7.5	1
11	 8	0         8mm
13	 9	00        9mm
15	 10	000	  10mm
17	 12	
19	 15	
35	 19
36	 20		
50	 25

When faced with a drawer full of mystery needles, a very useful tool is a needle gauge. It is a small piece of plastic or metal punched with graded holes. My favorite is a KnitChek by Susan Bates, and it covers the most common sizes; US#1-15. The holes are marked in US and metric, and crochet hook sizes, which is convenient.

reference other than knitting needle packages:

Erlich, Lola ed. Vogue Knitting: the ultimate knitting book, Sixth & Spring Books, 2002. - I visited this site today to double check sizes, and found a whole lot of needles and sets and accessories. Like the addict I am, I bought a book. Favorite discount yarn site that also carries needles, tools (like swifts), and books.

Someone just passed on this url, and it had some more of the UK sizes. Also, googling for Japanese needle conversion, I found this which is a fantastic resource.

If I'm missing anything, let me know and I will add it!