CTE expressions are supported with the `prepareQuery` option. 

https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html

A prefix that will form the final query together with query. As the specified query will be parenthesized as a subquery in the FROM clause and some databases do not support all clauses in subqueries, the prepareQuery property offers a way to run such complex queries. As an example, spark will issue a query of the following form to the JDBC Source.

<prepareQuery> SELECT <columns> FROM (<user_specified_query>) spark_gen_alias

Below are a couple of examples.

1. MSSQL Server does not accept WITH clauses in subqueries but it is possible to split such a query to prepareQuery and query:
   spark.read.format("jdbc")
.option("url", jdbcUrl)
.option("prepareQuery", "WITH t AS (SELECT x, y FROM tbl)")
.option("query", "SELECT * FROM t WHERE x > 10")
.load()
2. MSSQL Server does not accept temp table clauses in subqueries but it is possible to split such a query to prepareQuery and query:
   spark.read.format("jdbc")
.option("url", jdbcUrl)
.option("prepareQuery", "(SELECT * INTO #TempTable FROM (SELECT * FROM tbl) t)")
.option("query", "SELECT * FROM #TempTable")
.load()