Python Regex: Select everything between two brackets ignore brackets in between

I want to capture the entire sub-query irrespective of whether there’s a concat or substring function in between (i.e. ignore another bracket opening and closing within sub-query. (a) We don’t want to capture “join” as a word (b) “alias2” will not always be followed by “join” it can be anything (a word boundary, space, or “join” word).