Music can make all the difference in the world in a movie. It holds that it can make a lot of difference in a game. Mood music, when used effectively can strongly enhance the roleplaying experience. What you use, and how you use it is largely determined by the specific game. Still, there are some general guidelines you can use.
First, you should find music appropriate to the specific game. If, for example, you’re running a game of Call of Cthulhu, set in the default year of 1927, you might search for some radio hits of the era, some light classical music, or early jazz. However, if you are running a game of Star Wars, it might be more appropriate to find some ambient electronica or again, classical. For Ars Magica, I lean toward Gregorian chant or some of the more ‘primitive’ sounding music of Europe, including some of the Norse Revival or Celtic Revival of the last 30 years or so. I especially like Hedningarna for this. But there are several other good options, and I’m sure plenty I don’t know about.
Film soundtracks can be an invaluable resource, but can also be dangerous. You want something that matches the mood, but don’t want something too recognizable that might pull the players out of whatever they’re doing. If again, you’re running Star Wars, it wouldn’t be out of the question to play a couple Star Wars scores, but dropping in something from Lord of the Rings, especially one of the more memorable tracks. One go-to score for any action adventure type game is Stargate. It’s just generic enough that it sounds vaguely familiar without being easy to identify.
Music with words can also be a dangerous prospect. Too easy to understand, too well known and your players will be singing along or again, not paying attention to the game at hand. That said, like the clever use of a certain tune in movies, it can work well in a game, too. I think back to the blazing gun battle in Wild Palms set to The Animals version of House of the Rising Sun. While tricky, recreating this kind of moment in a game can be most satisfying. However, once again, appropriate music can be key. You wouldn’t want to use a hit from the 1970s in 1220s set Ars Magica, for example (unless you really think it’ll work; I just don’t see how it would…but then there’s the whole finale of Battlestar Galactica, which threw all sorts of rules out the window).
I’ve found myself collecting a heck of a library of odd, obscure, old, and just interesting music to use for almost any occasion. From heavy industrial for cyberpunk games, to Tibetan Buddhist chant which is great for creepy science fiction, to Native American flute music for…well, I never really found a good use for that. Short of using music, one might also use nature sounds that also might seem appropriate.
Again, like in movies, if used poorly the music can be a detriment to the overall enjoyment of the game, pulling players out of the proper headspace. So, be cautious and plan ahead. But it is worth the effort and can be a fantastic addition to a night of collective storytelling.