Why do people find horror games fun?

D&D, Pathfinder & OSR games can do horror with a few caveats. One is atmosphere, another is the use of monsters/encounters that get around the usual way combat functions (i.e. the use of monsters that are immune to broad damage types or non-magical weaponry, etc.) The party being relatively low level (1-6) also helps. Much beyond that and the range of skills and abilities they have puts them more into the superhero range of things.

My experience has been with older editions of D&D though. I'm not sure of how things have substantially changed since D&D 3.5 or Pathfinder 1ed but I think the overall advice stands.

But to answer Bookrat's original question:

I think there's players who enjoy the horror theme in games and those who don't. It comes down to mindset, really. There are players for whom the story matters more than their character and there are player for whomever their character is what matters to them. For the latter, the horror theme in games comes off as detrimental to the way they enjoy the game. Horror promotes a downward spiral towards disability and death whereas the type of game they prefer enjoys an upward spiral of reward accumulation and growth.

For me, what makes a horror game is the players agreeing they are gonna play a horror game. You can have all the ghosts, and all the monsters, and all the tentacles in the world in your game and it won't make it horror if the players have decided they won't be fazed by it. And that doesn't go only for DnD and PF and other games where the PCs are larger than life heroes with basically superpowers. It could be a CoC game in which the players are only interested in solving the mystery and trying to pick the most efficient solution.

So, basically, for me the appeal is certain aspects of the roleplay. It's not a mechanic that's telling you the PC should be scared.

Fear is our oldest and most primal emotion. It makes sense there would be extreme reactions to it on either side. Fear is not horror though. Terror, Disgust, and Dread are horror.

For instance Alien is a Horror movie first, a Sci-Fi second.

These themes are incredibly difficult to maintain the suspension of disbelief for. Which is why I try to make horror videos for the state of the art. The objective of horror is to change someone's mind not to scare them necessarily.

Perhaps the person against horror is thinking to rigidly and closes their minds to possibilities?

I love horror movies. In my games, I like fear. But, I still want to be the hero in the end.