SnowConvert AI - ANSI SQL - サブクエリ

説明

サブクエリは、別のクエリ内のクエリです。FROM 句または WHERE 句のサブクエリは、これを含むクエリが返すデータを制限または比較/評価するために使われるデータを提供するために使用されます。（ Snowflakeサブクエリのドキュメント ）

サブクエリには、スカラー型/非スカラー型のほか、相関型/無相関型もあります。

相関サブクエリ は、外部クエリの列を参照します。Snowflakeでは、相関サブクエリはクエリ内の各行に対して実行されます。一方、 無相関サブクエリ は外部クエリを参照せず、クエリ全体に対して一度だけ実行されます。

スカラーサブクエリ は結果として単一の値を返します。そうでないサブクエリは 非スカラー です。

以下のパターンは、これらのカテゴリに基づいています。

サンプルソースパターン

セットアップデータ

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

相関スカラーサブクエリ

Snowflakeは コンパイル時 に相関サブクエリを評価し、サブクエリがスカラーであって単一の戻り値が期待される文脈で妥当であるかどうかを判断します。これを解決するために、結果が集約関数でない場合、ANY_VALUE 集約関数が返された列に追加されます。これによってコンパイラーは、期待される戻り値が1つであると判断できます。スカラーサブクエリは単一の値を返すことが期待されているため、関数 ANY_VALUE は結果を変更せず、元の値をそのまま返します。

Teradata

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

結果

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

結果

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

無相関スカラーサブクエリ

Snowflakeは無相関スカラーサブクエリを完全にサポートしています。

Teradata

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

結果

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

Snowflake

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

結果

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

非スカラーサブクエリ

サブクエリ演算子（ANY/ALL/IN/EXISTS）内で指定された非スカラーサブクエリはサポートされています。

派生テーブルとして使用される非スカラーサブクエリもサポートされています。

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;

結果

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

結果

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

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

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

既知の問題

1.無相関スカラーではない FETCH firstを含むサブクエリ

Oracleではサブクエリで FETCH 句を使用できますが、Snowflakeではこの句を使用できるのはサブクエリが無相関スカラーの場合のみであり、そうでない場合は例外が発生します。

SnowConvert AI は、サブクエリでの FETCH の使用が無効である場合に 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);