SnowConvert AI - ANSI SQL - Sous-requêtes

Description

Une sous-requête est une requête dans une autre requête. Sous-requêtes dans une clause FROM ou [WHERE(https://docs.snowflake.com/en/sql-reference/constructs/where) est utilisée pour fournir des données qui seront utilisées pour limiter ou comparer/évaluer les données renvoyées par la requête les contenant. (Documentation des sous-requêtes Snowflake).

Les sous-requêtes peuvent être corrélées/non corrélées ainsi que scalaires/non scalaires.

Les sous-requêtes corrélées font référence aux colonnes de la requête externe. Dans Snowflake, des sous-requêtes corrélées s’exécutent pour chaque ligne de la requête. En revanche, les sous-requêtes non corrélées ne font pas référence à la requête externe et sont exécutées une fois pour la requête entière.

Les sous-requêtes scalaires renvoient une seule valeur comme résultat, sinon la sous-requête est non scalaire.

Les modèles suivants sont basés sur ces catégories.

Modèles d’échantillons de sources

Données de configuration

Teradata

CREATE TABLE tableA
(
    col1 INTEGER,
    col2 VARCHAR(20)
);

CREATE TABLE tableB
(
    col3 INTEGER,
    col4 VARCHAR(20)
);

INSERT INTO tableA VALUES (50, 'Hey');
INSERT INTO tableA VALUES (20, 'Example');

INSERT INTO tableB VALUES (50, 'Hey');
INSERT INTO tableB VALUES (20, 'Bye');

Snowflake

CREATE OR REPLACE TABLE tableA
(
    col1 INTEGER,
    col2 VARCHAR(20)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "12/02/2024",  "domain": "test" }}'
;

CREATE OR REPLACE TABLE tableB
(
    col3 INTEGER,
    col4 VARCHAR(20)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "12/02/2024",  "domain": "test" }}'
;

INSERT INTO tableA
VALUES (50, 'Hey');

INSERT INTO tableA
VALUES (20, 'Example');

INSERT INTO tableB
VALUES (50, 'Hey');

INSERT INTO tableB
VALUES (20, 'Bye');

Sous-requêtes scalaires corrélées

Snowflake évalue les sous-requêtes corrélées au moment de la compilation pour déterminer si elles sont scalaires et donc valides dans le contexte où une seule valeur de retour est attendue. Pour résoudre ce problème, la fonction d’agrégation ANY_VALUE est ajoutée à la colonne renvoyée lorsque le résultat n’est pas une fonction d’agrégation. Cela permet au compilateur de déterminer qu’une seule valeur est attendue. Comme les sous-requêtes scalaires sont censées renvoyer une seule valeur, la fonction ANY_VALUE ne modifiera pas le résultat, elle renverra simplement la valeur d’origine telle quelle.

Teradata

SELECT col2
FROM tableA
WHERE col1 = (SELECT col3 FROM tableB WHERE col2 = col4);

Résultats

+------+
| col2 |
+------+
| Hey  |
+------+

Snowflake

SELECT
    col2
FROM
    tableA
WHERE col1 =
             --** SSC-FDM-0002 - CORRELATED SUBQUERIES MAY HAVE SOME FUNCTIONAL DIFFERENCES. **
             (
                 SELECT
                     ANY_VALUE(col3) FROM
                     tableB
                 WHERE
                     RTRIM( col2) = RTRIM(col4));

Résultats

+------+
| col2 |
+------+
| Hey  |
+------+

Sous-requêtes scalaires non corrélées

Snowflake prend entièrement en charge les sous-requêtes scalaires non corrélées.

Teradata

SELECT col2, (SELECT AVG(col3) FROM tableB) AS avgTableB
FROM tableA
WHERE col1 = (SELECT MAX(col3) FROM tableB);

Résultats

+------+-----------+
| col2 | avgTableB |
+------+-----------+
| Hey  | 35        |
+------+-----------+

Snowflake

SELECT
    col2,
    (
                 SELECT
                     AVG(col3) FROM
                     tableB
    ) AS avgTableB
            FROM
    tableA
            WHERE col1 = (
                 SELECT
                     MAX(col3) FROM
                     tableB
    );

Résultats

+------+-----------+
| col2 | avgTableB |
+------+-----------+
| Hey  | 35.000000 |
+------+-----------+

Sous-requêtes non scalaires

Les sous-requêtes non scalaires spécifiées dans des opérateurs de sous-requête (ANY/ALL/IN/EXISTS) sont pris en charge.

Les sous-requêtes non scalaires utilisées comme tables dérivées sont également prises en charge.

Teradata

SELECT col2
FROM tableA
WHERE col1 IN (SELECT col3 FROM tableB);

SELECT col2
FROM tableA
WHERE col1 >= ALL(SELECT col3 FROM tableB);

SELECT col2, myDerivedTable.col4
FROM tableA, (SELECT * FROM tableB) AS myDerivedTable
WHERE col1 = myDerivedTable.col3;

Résultat

+---------+
| col2    |
+---------+
| Example |
+---------+
| Hey     |
+---------+

+---------+
| col2    |
+---------+
| Hey     |
+---------+

+---------+------+
| col2    | col4 |
+---------+------+
| Example | Bye  |
+---------+------+
| Hey     | Hey  |
+---------+------+

Snowflake

SELECT
    col2
            FROM
    tableA
            WHERE col1 IN (
                 SELECT
                     col3 FROM
                     tableB
    );

                     SELECT
    col2
            FROM
    tableA
            WHERE col1 >= ALL(
                 SELECT
                     col3 FROM
                     tableB
    );
                    SELECT
    col2,
    myDerivedTable.col4
            FROM
    tableA, (
                 SELECT
                     * FROM
                     tableB
    ) AS myDerivedTable
            WHERE col1 = myDerivedTable.col3;

Résultats

+---------+
| col2    |
+---------+
| Example |
+---------+
| Hey     |
+---------+

+---------+
| col2    |
+---------+
| Hey     |
+---------+

+---------+------+
| col2    | col4 |
+---------+------+
| Example | Bye  |
+---------+------+
| Hey     | Hey  |
+---------+------+

Problèmes connus

1. Sous-requêtes avec FETCH d’abord qui ne sont pas des sous-requêtes scalaires non corrélées

Oracle permet d’utiliser la clause FETCH dans les sous-requêtes, Snowflake n’autorise l’utilisation de cette clause que si la sous-requête n’est pas scalaire non corrélée, sinon une exception sera générée.

SnowConvert AI marquera toute utilisation non valide de FETCH dans les sous-requêtes avec SSC-EWI-0108

Oracle :

-- Correlated scalar
SELECT col2
FROM tableA
WHERE col2 = (SELECT col4 FROM tableB WHERE col3 = col1 FETCH FIRST ROW ONLY);

-- Uncorrelated scalar
SELECT col2
FROM tableA
WHERE col2 = (SELECT col4 FROM tableB FETCH FIRST ROW ONLY);

Snowflake :

-- Correlated scalar
SELECT col2
FROM
    tableA
    WHERE col2 =
                 --** SSC-FDM-0002 - CORRELATED SUBQUERIES MAY HAVE SOME FUNCTIONAL DIFFERENCES. **
                 !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!! (SELECT
                         ANY_VALUE( col4) FROM
                         tableB
                     WHERE col3 = col1
                     FETCH FIRST 1 ROW ONLY);
 
 -- Uncorrelated scalar
SELECT col2
FROM
    tableA
    WHERE col2 = (SELECT col4 FROM
                         tableB
                     FETCH FIRST 1 ROW ONLY);