In the classic spatial Finder, every folder always opened in a new window. I'm not sure if it's possible to force that behavior under the current Finder.
The current Finder does that thing sometimes, and other times it doesn't. I'm not sure what the rule is, it might be related to whether it's a folder you double-click directly from the desktop.
It does it on read only filesystems. So you’ll see it if you plug in an ntfs drive, for example (unless you’ve installed third party ntfs drivers).
which has never made any sense to me - what's different about a read-only FS that would make me want every single navigation action to open a new window?
I think the closest you can get is holding down CMD when you open a folder. There doesn't seem to be a way to get that to be the default.
That seems to just control whether holding down CMD and opening a folder will open in a tab or a window. Even with that unchecked, folders still open in-place by default.