When I try the following test of a subquery in SFMC Query Studio, I get the error message "Unable to create temporary data extension: Field name cannot be blank."
According to official Salesforce documentation, SFMC's flavor of SQL is SQL Server 2016 with some listed limitations, and subqueries are not mentioned as not supported. And according to official SQL Server 2016 documentation my syntax below is correct.
Note that this is just a test query for simplicity's sake, and is not supposed to have practical value.
SELECT Id, ( SELECT StageName FROM Opportunity_Salesforce AS o1 WHERE o1.ContactId = c.Id ) AS test FROM Contact_Salesforce AS c