SnowConvert AI – ANSI SQL – Unterabfragen

Beschreibung

Eine Unterabfrage ist eine Abfrage in einer anderen Abfrage. Unterabfragen in einer FROM- oder WHERE-Klausel werden verwendet, um Daten bereitzustellen, die zum Begrenzen oder Vergleichen/Auswerten der von der enthaltenden Abfrage zurückgegebenen Daten verwendet werden. (Dokumentation zu Snowflake-Unterabfragen).

Unterabfragen können sowohl korreliert/unkorreliert als auch skalar/nicht skalar sein.

Korrelierte Unterabfragen verweisen auf Spalten aus der äußeren Abfrage. In Snowflake werden korrelierte Unterabfragen für jede Zeile in der Abfrage ausgeführt. Unkorrelierte Unterabfragen verweisen hingegen nicht auf die äußere Abfrage und werden einmal für die gesamte Abfrage ausgeführt.

Skalare Unterabfragen geben einen einzelnen Wert als Ergebnis zurück, andernfalls ist die Unterabfrage nicht skalar.

Die folgenden Muster basieren auf diesen Kategorien.

Beispielhafte Quellcode-Muster

Datenkonfiguration

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');

Korrelierte skalare Unterabfragen

Snowflake wertet korrelierte Unterabfragen zur Kompilierungszeit aus, um festzustellen, ob sie skalar und daher in dem Kontext gültig sind, in dem ein einzelner Rückgabewert erwartet wird. Um dies zu lösen, wird die ANY_VALUE-Aggregatfunktion der zurückgegebenen Spalte hinzugefügt, wenn das Ergebnis keine Aggregatfunktion ist. Dies ermöglicht es dem Compiler, festzustellen, dass die Rückgabe eines einzelnen Werts erwartet wird. Da von skalaren Unterabfragen erwartet wird, dass sie einen einzelnen Wert zurückgeben, ändert die ANY_VALUE-Funktion das Ergebnis nicht, sondern gibt nur den ursprünglichen Wert wie gehabt zurück.

Teradata

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

Ergebnisse

+------+
| 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));

Ergebnisse

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

Unkorrelierte skalare Unterabfragen

Snowflake unterstützt unkorrelierte skalare Unterabfragen vollständig.

Teradata

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

Ergebnisse

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

Snowflake

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

Ergebnisse

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

Nicht skalare Unterabfragen

Nicht skalare Unterabfragen, die innerhalb von Unterabfrageoperatoren (ANY/ALL/IN/EXISTS) angegeben sind, werden unterstützt.

Nicht skalare Unterabfragen, die als abgeleitete Tabellen verwendet werden, werden ebenfalls unterstützt.

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;

Ergebnis

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

Ergebnisse

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

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

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

Bekannte Probleme

1. Unterabfragen mit FETCH zuerst, die nicht unkorrelierte skalare Unterabfragen sind

Oracle erlaubt die Verwendung der FETCH -Klausel in Unterabfragen, Snowflake erlaubt die Verwendung dieser Klausel nur, wenn die Unterabfrage nicht korreliert skalar ist; andernfalls wird eine Ausnahme generiert.

SnowConvert AI markiert jede unzulässige Verwendung von FETCH in Unterabfragen mit 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);