Common Table Expression in Sub-Query

Question

I would request for help in understanding which all RDBMS from Oracle, DB2, Sybase support a common table expression (CTE) in a sub-query. I am aware that PostgreSQL does while MS SQL Server does not.

SELECT a.*, b.* 
 FROM (WHERE aa as (
          <<select_query>),
          SELECT * 
            FROM aa
           WHERE <<criteria>>
    ) as a
    LEFT JOIN (
        WITH bb as (
            <<select_query>
        ),
        select * from bb inner join tbl_c on <<innerjoin>> where <<criteria>>
    ) as b
    on <<join_expr>>

I am unable to define the with clause outside the sub-queries - both the queries are dynamically generated w.r.t. the columns, criteria, security, etc. Also, the above query itself may be used in another query as a sub-query. In summary, the principle is dynamically generated views, re-usable later. Some queries may have upto 10-12 such dynamic views being merged together as well. The problem is that the application is supposed to be database-agnostic at least so far as PG, Oracle & DB2 are concerned and features not supported by one are not implemented at all.

Answer 1

Yes, you can use CTE's in subqueries in Oracle. From the Oracle 11g docs:

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries. For recursive subquery factoring, the query name is even visible to the subquery that defines the query name itself.

As an example, this works in Oracle:

SELECT a.*, b.*
  FROM (WITH aa AS
         (
           SELECT LEVEL l1, mod(level, 5) m1 FROM dual CONNECT BY LEVEL < 50
         )
         SELECT * FROM aa WHERE m1 < 3) a LEFT JOIN
       (WITH bb AS 
         (
           SELECT LEVEL l2, mod(level, 5) m2 FROM dual CONNECT BY LEVEL < 50
         )
         SELECT * FROM bb WHERE m2 BETWEEN 1 AND 4) b
        ON a.l1 = b.l2;

Answer 2

That's not directly an answer to your question, but maybe you can think about this:

SQL Server seems to limit the semantics (not necessarily the syntax) of SQL where it makes sense to do so. For instance, you cannot have a subquery with an ORDER BY clause, if you don't also specify a TOP n clause. This makes sense, as ordered subqueries are pointless unless they have limits. Other RDBMS allow for such pointlessness.

In your case (that's just a guess), having CTE's in subqueries only makes limited sense, because you can rewrite your whole query in a way that the CTE's are declared at the top-most level. The only difference you'll have is the scope and maybe the readability of each declaration.

On the other hand, CTE's allow for recursive queries, which might be very hard to apply when CTE's are declared in subqueries...

Since you need to implement database-agnostic SQL, I recommend you do not make heavy use of CTE's yet. If CTE's are simple, you can always rewrite them as simple views...