Left 4 Dead, much like the “[NOUN] of the Dead” movies, doesn’t really make much of any attempt to scare you at any sort of deep psychological level. Humans are very adaptable, and once their adversary has a face and can be seen and analyzed, it becomes less frightening. L4D throws the enemies right into your face (often clipping through each other horribly, reminding you there’s nothing real here), hell it NAMES them for you, and gives you all the tools you need to survive through them.
While it’s certainly not a grand masterpiece of writing, They Hunger for HL1 does a much better job with building a more psychologically unsettling atmosphere. You’re not always armed to the teeth with hardcore weapons and tools, but rather what you were blessed to find here and there amongst the remains of those who may or may not be less fortunate than yourself. For pretty much the whole game, it is you and only you, and your enemy isn’t always very conspicuous.
I’m sure some of your more terrifying nightmares involved a threat you couldn’t see, just simply aware of its existence nearby. That is what terrifies our little ape brains, an enemy we know nothing about other than the fact that it exists, and very likely is after us. L4D doesn’t even toy around with reminding you of the notion that you, yourself, may be doomed to become the walking dead, and that such an existence may be a horrible one beyond your imagination.
L4D is a fast-pace, action-packed clusterfuck co-op shooter, and while it may be exciting and thrilling in that manner, its fear tactics are pretty shallow. It just brings you to the edge of your seat, rather than taunting the very base of your instincts.