In all cases, Spring generates the class that provides the implementation of the method. When you provide a query in the Query annotation, the method generated by spring will use the query specified in your Query annotation. When you do not provide a Query annotation, Spring will create a JQL queryby parsing the method name. For example, findByEmailAddress will be converted to Select u for User u where emailAddress= ?
Query annotation supports JQL queries and native SQL queries. It uses your underlying JPA provider which in turn runs JDBC queries against the database. So, the kind of queries that you can run will be limited to the JPA provider and the database. If the database doesn't support nested queries then Spring Data won't support nested queries
Look at the documentation of the JPA provider and database to figure out if nested queries are supported.