So what games do you think do a good job of capturing this risk/reward dynamic of wanting to explore a dangerous space?
All well-designed open world games do this, imho. Open world games with no really dangerous spaces are doomed to be boring after a while.
I remember that one of the first open world games I ever played —
The Elder Scrolls: Arena (1994) — did this well. (And remember, that's at a time when open world exploration was very limited because of the various technical reasons.) You could, for example, go to cemeteries and enter tombs, to lift some gold and equipment from the deceased. Most of the time the only opposition were some bats and rats.
But sometimes, you'd encounter the deceased himself.
And let me tell you, that was scary as f***, even in 640x400 and 256 colors. I remember the first time this happened to me, I was running away from the cemetery for some very long minutes before I dared to even look behind. I also remember talking about this experience with my friend who also played Arena at that time. These experiences are a major part of the fun I get from gaming.
On the most basic level, I think it's a combination of two things:
- The slot machine mechanic — it must be random, and it must have outliers in terms of both reward and risk. Put a small chance that the player either find something truly great, or stumbles upon something truly horrible.
- A good sense of danger — I believe these experiences must be crafted in the same way a horror scene would be. There must be some kind of precognition for things to be scary. That's why most horror books have some character who 'somehow' knows in advance that there's going to be trouble. That's why you hear the growling in Minecraft at night. The monsters could just as well be completely silent, and they'd be more dangerous if they were, but then the exploration would really feel like playing a slot machine and nothing more.
To answer your original question: all Elder Scrolls games, Fallout, Ultima, Planescape: Torment... I'd say even GTA and Saints Row series have this but they don't do so well at the second point (sense of danger) — because they focus on other kinds of player enjoyment than exploration.