It is far simpler to have a texture of some kind, which can be obtained in a number of ways, that is mathematically 'mapped' onto a surface. Compared to flat shading something like planetary terrain, using texture mapping can reduce the number of polygons used by a factor of as much as 200:1. Figure 1 above shows the difference between using and not using texture mapping (click on the image for a much larger version). Nevertheless, a larger number of smaller polygons still means better detail (compare the two landscape images shown above).
Texture mapping in its simplest form involves a single texture being mapped onto a flat surface composed of one or more polygons, eg. an artist's drawing of a metal door is mapped onto a rectangular vertical polygon (in pratice, a rectangular polygon would be treated as at least two triangles), or a photograph of a wooden crate is mapped onto the flat sides of a cube. Imagine, for a moment, how you might create a realistic virtual model of a room by taking photographs of all the various exposed surfaces (walls, tables, book shelves, etc.), scanning them in and using the digital images as textures in a 3D model; without the textures, the surfaces would only have simple colour and shade. Adding textures gives much greater realism.
Since a curved surface can be made up from several smaller triangles or rectangles, objects like oil barrels and stone columns can be effectively modeled by the careful use of textures on the multiple surfaces involved; as long as the edges match closely, the resulting objects can look quite realistic. Textures can also be repeated horizontally and/or vertically across a surface (with our without a degree of rotation), a technique used extensively for modeling surfaces such as brick walls, grass, roads, fences, etc.
You'll find these methods used extensively in most 3D games; the examples given here are from Goldeneye.
Texture mapping becomes alot more useful and powerful when a texture can be mapped onto increasingly complex surfaces automatically, such as cubes, spheres, cylinders or arbitrary 3D surfaces (eg. a landscape data set). In fact, the latter is how many flight simulators work: ariel photgraphs are used as a source of texture data whilst 3D terrain data can be obtained from existing map data or from stereo processing of satelite images. The results can be very realistic indeed.
The Earth image shown on the left was created by mapping the flat rectangular picture of Earth on the right onto an ordinary sphere. The warping was done automatically - I could equally well have mapped the picture onto a cube, a cone, or any arbitrary 3D model. Once a texture has been applied, adding lighting effects using Gouraud shading (or other technique) enhances the realism further - in the example here, adding a simple directional light gives a very nice illuminated Earth model (click on the left-hand picture to download a full-size 226K 1197x876 JPEG image), though the astute viewer will notice the light is from completely the wrong angle (you'll need to examine the complete picture to see why :)
Textures can be applied in two ways: either the texture replaces whatever colour is already inherant to the polygon, or the texture colour is blended with the colour and surface properties of the polygon (inlcuding transparency data) and will thus look different on surfaces of different colour and character. Operating on a per-pixel basis, these techniques - when combined with other effects such as Gouraud shading (ie. a texture is affected by light sources), transparency, depth cueing and environment mapping, offer great flexibility for producing realistic imagery.
These days, texture mapping is not something that is hard to do, but it is something that should be done with an eye towards quality and accuracy. When I watch PSX or Saturn games, I often notice textues 'swimming' about in an unstable manner, or polygonal surfaces seem to wobble around, showing gaps between polygons (Tomb Raider is one example). Textures are also pixelated and blocky-looking. Such effects ruin the immersion of the game since the realism is spoiled (at least for me anyway). It is important in texture mapping to have accuracy and that is something that SGI is famous for as any user of their traditional workstations can attest to. The N64's RCP is based on SGI-designed technology (a designed called RealityEngine, used in the early Crimson system) and as such displays good accuracy: if you watch a game like Turok, the scenery is very stable and clean - textures don't swim around or wobble about. When it comes to realism and immersion, such high quality is important.
SGI has a good page on texture mapping; it gives further examples and uses, so I recommend you read it. I've also typed up a document from SGI which shows how texture mapping can be used to improve visualisation of molecular modeling, etc.
If you'd like to see some examples of high quality texture mapping, then download the file diss2.zip from my BSc Dissertation archive.