The answer to this question depends on what model of the Apple TV you have. If you've got the second-generation model, the answer is simple: No. The second-generation Apple TV doesn't work with standard-definition TVs.
If you've got the original model, though, your Apple TV will work with any TV with component inputs. Component inputs are the five plugs clustered close together. Three of them - red, green, and blue - are for video, while two of them - white and red - are for audio. If your TV has all five, there's a good chance the Apple TV will work with it.
In this case, though, the quality of the images you see onscreen will only be as good as your TV. You can rent or buy HD content using the Apple TV, but won't be able to see it in HD, since your TV is standard definition.
But, if you've got a TV with component inputs and want to get content from your iTunes library or the iTunes Store to your TV, give it a shot.