I know that match exists, but I need to run the equivalent of SELECT foo WHERE bar IN (SELECT baz WHERE qux='quux'). I have looked at the docuemtation and there is no mention of a way to do this, at least where I would expect it to be (see here). So how can I make complex queries such as this one?
You can mimic the IN operator by concatenating the inner query results with JOIN using the pipe character and evaluating the resultant array using MATCHES (which interprets the pipes as ORs):
=query(array,"select foo where bar matches '"&join("|",query(array,"select baz where qux='quux'))&"'")
If your inner query has a header row, you'll need to add label baz '' to the end of the select string to remove the header otherwise you'll be trying to match that as well.