Notes that sound good together when played at the same time are called consonant. Chords built only of consonances sound pleasant and "stable"; you can listen to one for a long time without feeling that the music needs to change to a different chord. Notes that are dissonant can sound harsh or unpleasant when played at the same time. Or they may simply feel "unstable"; if you hear a chord with a dissonance in it, you may feel that the music is pulling you towards the chord that resolves the dissonance. Obviously, what seems pleasant or unpleasant is partly a matter of opinion. This discussion only covers consonance and dissonance in Western music.
Of course, if there are problems with tuning, the notes will not sound good together, but this is not what consonance and dissonance are about. (Please note, though, that the choice of tuning system can greatly affect which intervals sound consonant and which sound dissonant! Please see Tuning Systems for more about this.)
Consonance and dissonance refer to intervals and chords (Chords). The interval between two notes is the number of half steps between them, and all intervals have a name that musicians commonly use, like major third (Major and Minor Intervals) (which is 4 half steps), perfect fifth (7 half steps), or octave. (See Interval to learn how to determine and name the interval between any two notes.)
An interval is measured between two notes. When there are more than two notes sounding at the same time, that's a chord. (See Triads, Naming Triads, and Beyond Triads: Naming Other Chords for some basics on chords.) Of course, you can still talk about the interval between any two of the notes in a chord.
The simple intervals that are considered to be consonant are the minor third, major third, perfect fourth, perfect fifth, minor sixth, major sixth, and the octave.
In modern Western Music, all of these intervals are considered to be pleasing to the ear. Chords that contain only these intervals are considered to be "stable", restful chords that don't need to be resolved. When we hear them, we don't feel a need for them to go to other chords.
The intervals that are considered to be dissonant are the minor second, the major second, the minor seventh, the major seventh, and particularly the tritone, which is the interval in between the perfect fourth and perfect fifth.
These intervals are all considered to be somewhat unpleasant or tension-producing. In tonal music, chords containing dissonances are considered "unstable"; when we hear them, we expect them to move on to a more stable chord. Moving from a dissonance to the consonance that is expected to follow it is called resolution, or resolving the dissonance. The pattern of tension and release created by resolved dissonances is part of what makes a piece of music exciting and interesting. Music that contains no dissonances can tend to seem simplistic or boring. On the other hand, music that contains a lot of dissonances that are never resolved (for example, much of twentieth-century "classical" or "art" music) can be difficult for some people to listen to, because of the unreleased tension.
Why are some note combinations consonant and some dissonant? Preferences for certain sounds is partly cultural; that's one of the reasons why the traditional musics of various cultures can sound so different from each other. Even within the tradition of Western music, opinions about what is unpleasantly dissonant have changed a great deal over the centuries. But consonance and dissonance do also have a strong physical basis in nature.
In simplest terms, the sound waves of consonant notes "fit" together much better than the sound waves of dissonant notes. For example, if two notes are an octave apart, there will be exactly two waves of one note for every one wave of the other note. If there are two and a tenth waves or eleven twelfths of a wave of one note for every wave of another note, they don't fit together as well. For much more about the physical basis of consonance and dissonance, see Acoustics for Music Theory, Harmonic Series, and Tuning Systems.