oracle - tipos - Expresión común de tabla en sub-consulta

subconsultas base de datos

Question

Pediría ayuda para entender qué todos los RDBMS de Oracle, DB2, Sybase admiten una expresión de tabla común (CTE) en una sub consulta. Soy consciente de que PostgreSQL lo hace, mientras que MS SQL Server no.

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>>

No puedo definir la cláusula with fuera de las subconsultas: ambas consultas se generan dinámicamente con las columnas, los criterios, la seguridad, etc. Además, la consulta anterior puede usarse en otra consulta como una subconsulta. En resumen, el principio son las vistas generadas dinámicamente, reutilizables más adelante. Algunas consultas pueden tener hasta 10-12 vistas dinámicas fusionadas juntas. El problema es que se supone que la aplicación es independiente de la base de datos, al menos en lo que se refiere a PG, Oracle y DB2, y las características que no son compatibles con ninguna se implementan en absoluto.

Answer 1

Esa no es una respuesta directa a su pregunta, pero tal vez pueda pensar en esto:

SQL Server parece limitar la semántica (no necesariamente la sintaxis) de SQL donde tiene sentido hacerlo. Por ejemplo, no puede tener una subconsulta con una cláusula ORDER BY , si no especifica también una cláusula TOP n . Esto tiene sentido, ya que las subconsultas ordenadas son inútiles a menos que tengan límites. Otros RDBMS permiten tal inutilidad.

En su caso (eso es solo una suposición), tener CTE en subconsultas solo tiene un sentido limitado, porque puede reescribir toda su consulta de manera que los CTE se declaren en el nivel más alto. La única diferencia que tendrá es el alcance y tal vez la legibilidad de cada declaración.

Por otro lado, los CTE permiten consultas recursivas, que pueden ser muy difíciles de aplicar cuando los CTE se declaran en subconsultas ...

Como necesita implementar SQL independiente de la base de datos, le recomiendo que no haga un uso intensivo de CTE. Si los CTE son simples, siempre puede reescribirlos como simples vistas ...

Answer 2

Sí, puede usar CTE en subconsultas en Oracle. De los documentos de Oracle 11g :

Puede especificar esta cláusula en cualquier instrucción SELECT de nivel superior y en la mayoría de los tipos de subconsultas. El nombre de la consulta es visible para la consulta principal y para todas las subconsultas posteriores. Para la factorización de subconsulta recursiva, el nombre de la consulta es incluso visible para la sub consulta que define el nombre de la consulta en sí.

Como ejemplo, esto funciona en 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 3

Las versiones más nuevas de Microsoft SQL Server son compatibles con CTE.

Answer 4

Si bien PostgreSQL admite CTE, son una barrera de optimización que evita el predicado o la inserción en la consulta CTE. Esto los hace menos efectivos en muchos casos que una simple subconsulta.