Finder behaviour is technically consistent, all things being equal, but it's inconsistent in practice as a knock-on effect of other limitations: the root of this is the (inability) to quit Finder*. If you never quit any apps you ever use, you could probably get consistent behaviour across them, but broadly speaking if you do quit apps you're not using, opening those apps will tend to bring up a new window. Since Finder can't be quit, "opening" it afresh constitutes switching to it in its windowless state. Technically consistent (same as switching to any windowless app) but inconsistent in practice (switching to any other app you have quit will bring a window to the fore).

I suspect this is one of those engineers-vs-ux things where engineers will close this as "works as intented" without considering that the "way it's supposed to be used" isn't necessarily a good model for actual user behaviour. The other potential excuse is "but Desktop icons", which is just rooted in the leaky abstraction of devs tending to DRY-ify desktop rendering by hard-linking it to the app that provides file mgmt. windows.

*: You can work around this with the `com.apple.finder QuitMenuItem` setting at the cost of Desktop icons, but we're talking default settings here.