Sandpaper is expensive and there is a lot of it to choose from, so you want to get the paper that will do whatever sanding job you want to do most efficiently. Whether you are working by hand or with some kind of a power tool, the right sandpaper is more important than the tool itself.
Look on the back of the sheets of paper and you will discover a lot of information.
TYPE OF PAPER
If you come across FLINT paper, sometimes called GLASS paper (not because it is for use with glass but because it is made out of glass) -- don't even buy it. This is the original sandpaper and it just doesn't work. The grit falls off and although it is quite cheap per sheet, if you actually try to do a job with it, it will come out to be more expensive because it does so little work before it is dead. Buying Flint Paper is the best way to end up hating sanding.
GARNET paper is inexpensive sandpaper that can work well with soft woods, but wears out quickly on hard woods and other materials. However, on soft woods it actually has an advantage. Most little grains of sandpaper start with sharp edges and they slowly get dull. Garnet grits work differently: rather than wear off, they break off, leaving a new sharp little grit. So Garnet paper doesn't get dull slowly, it sands very well right until there is no more grit. It is rather easy to see when you need a new piece of paper. It is probably the standard for working with pine and cedar.
ALUMINIUM OXIDE paper is far more durable than Garnet paper so it can be used well on harder surfaces. It does get dull with time and can fool you into thinking it is still cutting, keep your eye out to see if you are getting no more dust. Continuing on with dull paper creates heat which can cause clogging or finishing problems. Aluminium Oxide paper works very well on all wood and can do light jobs on metal and plastic. It is available with a cloth backing and in wet/dry formats for use with water and oil. If you are only working with soft wood, don't waste your money; stick with Garnet paper.
SILICONE CARBIDE paper is very durable so it is often used for polishing hard surfaces like plastics, and metal. It is even used to dress the edges of cut glass. Because of its superior durability it is often sold with special backings, some of them that can be used under water or oil -- allowing fine polishing. Perhaps the best way to think about silicone carbide is as more of a polishing paper than a sanding paper.
OPEN COAT -- CLOSED COAT
One confusing aspect of sandpaper is understanding what is meant by 'open-coat' and 'closed-coat'. You will find both offered in the same sandpaper with the same grit. Grit refers to the size of the sanding granules. Open or closed coat refers to how far apart those granules are placed on the paper. So an open coat sandpaper can leave more scratch marks than its closed coat brother, but will tend to clog less. The more you are sanding a material that tends to clog, the more you need the open-coat, like for removing paint or working with resinous woods. If you are sanding something that does not clog, like dry clean wood, then you get smoother results with the closed coat.
GRITS
Grit refers to the size of the sharp little granules. Notice I said 'sharp'. Old sandpaper has not moved from 100 grit to 120 grit because it feels smoother. Old 100 grit sandpaper is dull 100 grit sandpaper, not sharp 120 grit paper. As with all tools we want to work with sharp edges. Rubbing hard with old sandpaper, either by hand or machine, only heats up the surface either melting what you are trying to remove, or sealing the surface you hope to stain. When you quit getting dust, change the paper, don't just push harder.
I categorize sandpaper grits into three groups: Removing material - Removing scratches - Polishing.
100 grit and less sandpaper is used to remove material. Do not move beyond any grit you start with until you have removed the material you want to remove. Keep changing paper but stay with the same grit. Never go beyond 100 grit until you have everything as flat and even as you want.
120 to 220 grits are used to remove the scratch marks from each lower grit, dealing with the kind of imperfection you see in the second photo above. This group of grit cannot remove any material rapidly. In fact, here you need to work your way through the grits as each one is only capable of removing the scratch marks from the one below it. If you roughed something out with 60 or 80 grit paper, you need to use the 100 grit paper to take the strong scratches out, as the 120 grit paper will only efficiently remove enough material to remove the scratches from the 100 grit. If you jump quickly from 100 to 220, you will be sanding forever trying to get the scratches out.
For most woodworking we quit at 220 grit before the finish. If we have scratches at this time, it is better to take 220 grit paper on a hand pad and carefully stroke the surface in the direction of the grain to remove any marks rather than move up to a finer sandpaper. The use of very fine sandpapers, especially on wood, tends to create heat and seal off the surface of the wood, causing stains and finishes to penetrate unevenly -- the same kinds of problems we get with dull sandpaper.
Above 220 grit falls into the polishing category. You really cannot remove much material with these papers. They are primarily used for polishing marble, plastic, glass edges and even metal. They can be very useful between coats of clear finishes as they will only remove the high spots without cutting into the coating.
So working diligently on bare pine with 400 grit silicone carbide paper in a power sander is not 'going for the best'; in fact it is neither good woodworking nor cost effective.