VSCode does support that. It's up to language extension authors to implement it though because obviously only they know which bit of text is in which language.

I don't think SQL queries in code is a very big use case and even there, any that are large enough to need syntax highlighting are probably better off in a separate file.

The biggest use case of multi-language files I've seen is Vue single file components, but in practice they turned out to be a terrible idea precisely because of tooling issues like this.