Decoupling capacitors are usually of the ceramic kind, sometimes combined with an electrolytic capacitor. Ceramic capacitors are widely used for most applications, the electrolytic ones are useful if you need higher capacitance values in a relatively small package. In most cases ceramic capacitors will fit your need just fine
I usually use the biggest footprint I can fit on to the board (0805-1206) unless I don't have the space. Capacitors with bigger footprints usually have a higher max voltage rating without a lot of added costs. Also they tend to have improved temperature stability. So the 0402 cap will probably have a reduced max rating, and slightly reduced temperature stability in comparison with the 0603. But if you are not planning on using the product in extreme environments and the Vmax of the 0402 fits your needs (including some extra tolerance as buffer) it should be fine.
Another thing to watch out for is the type of capacitor X5R, X7R and C0G/NP0 are less susceptible to a broader temperature range as Y5V.
This is I think pretty much the basics, other things that can be taken into account are the ESR/Q needed for example. Which can be of importance when designing filters of switching power supplies.
ADDITIONAL EDIT:
I just briefly want to add that if you are working with high frequency, where the performance is determined mostly on the inductance. That a 1206 SMT capacitor will have approximately twice the inductance of a 0603 capacitor. Therefor for high frequencies it is recommended to use the smallest practical size for the application. And find the largest capacitor value available in this size. (A smaller value will have no improvement in high frequency performance but will degrade low frequency effectiveness)