Common table expression in a subquery

Question

I would ask for help in understanding that all DBMSs from Oracle, DB2, Sybase support a common table expression (CTE) in a subquery. I know that PostgreSQL does this until MS SQL Server does.

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 cannot define a with clause outside of subqueries - both queries are dynamically generated by wrt columns, criteria, security, etc. In addition, the above query can be used in another query as a subquery. Thus, the principle is dynamically generated views that are reused later. Some queries can have up to 10-12 of these dynamic representations, which are also combined together. The problem is that the application must be database-aggregated, at least as long as PG, Oracle, and DB2 are involved, and functions not supported by one are not implemented at all.

Answer 1

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

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The name of the query appears in the main query and to all subsequent subqueries. For recursive factoring of subqueries, 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

This is not a direct answer to your question, but maybe you can think about it:

SQL Server seems to limit the semantics (not necessarily syntax) of SQL, where it makes sense to do so. For example, you cannot have a subquery with an ORDER BY unless you also specify a TOP n clause. This makes sense, since ordered subqueries are pointless if they have no restrictions. Other DBMSs allow such meaninglessness.

In your case (this is just a hunch), having CTEs in subqueries has limited meaning, because you can rewrite the entire request so that CTEs are declared at the highest level. The only difference you will have is the scope and possibly the readability of each declaration.

CTE, on the other hand, allows recursive queries, which can be very difficult to apply when CTEs are declared in subqueries ...

Since you need to implement SQL agnostic SQL, I recommend that you not use CTE yet. If CTEs are simple, you can always rewrite them as simple representations ...

Answer 3

Newer versions of Microsoft SQL Server support CTE.

Answer 4

While PostgreSQL supports CTEs, they are an optimization barrier that prevents a predicate or join from going into a CTE query. This makes them less efficient in many cases than a simple subquery.