Depends on the developers how much they want to scare the gamers...
RE 7 is most acclaimed in Horror genre.
Outlast is the king of horror games. Though there are many good Indie Horror games.
I heard the recent Blairwitch game is also has good horror
Haven't played the Blair Witch game but from the videos I've seen the story deviates from the supernatural to the protagonists personal story a bit too much. That kind of spoils the premise.
Recent Remake of RE isn't a horror game, Original can be classified as a horror game. Recent remake is too Hollywood to be scary.
My personal opinion
Alien Isolation is an example of a AA game, me thinks btw.
IDK, I found it pretty scary in the beginning. RE2 at least, haven't purchased the RE3 remake. When I used to hear Mr. X's footsteps, I used to hide and try to find out where he is before moving. If everything is silent, I would still stand still trying to see whether it comes back. Plus the limited ammo used to put pressure on me because I don't know when I will have to face the next zombie. But once I figured out where everything is, I can now zoom through the game pretty quickly. So, now it's not so scary.
RE3 does look like a action movie though with the dodge mechanics and automatic weapons.
But I'd still put RE2 on the same level as Alien Isolation, if a but shorter.