The scariest for me is still Best of Both Worlds, Part 1. I was literally an infant when it first aired, but I remember seeing it in reruns when I was maybe five or six years old.
When I was a child, Captain Picard was almost a god to me — not in a hero worship kind of way, but almost a literal sense. He was firm but kind, intelligent but humble, and always did the right thing no matter what it meant for himself. And Patrick Stewart even sounds like what I thought God's voice might be like.
The Borg had been looming in the background all episode, and then when they finally show up they stole Picard, corrupted him, made him into a monster that wanted to hurt everyone Picard cared for, and forced Riker (who I vaguely remember believing as a kid was Picard's son) to kill him... it felt like the whole world was crashing down. And worst of all Captain Picard wasn't going to be there to put it back together, because the Borg just took him.
ETA: and when the away team is trying to explain to Riker what they saw, Worf sounds so lost and angry when he just shouts "He is a Borg". It was a terrifying, almost traumatic experience for me.
And then the episode just... stopped. I was so young I'd never experienced a cliffhanger before. And to this day I bet I could count on one hand the number of cliffhangers that hit me as hard.