SnowConvert AI - ANSI SQL - Subconsultas

Descrição

Uma subconsulta é uma consulta dentro de outra consulta. Subconsultas em uma cláusula FROM ou WHERE são usadas para fornecer dados que serão usados para limitar ou comparar/avaliar os dados retornados pela consulta que os contém. (Documentação das subconsultas do Snowflake).

As subconsultas podem ser correlacionadas/não correlacionadas, bem como escalares/não escalares.

Subconsultas correlacionadas referenciam colunas da consulta externa. No Snowflake, as subconsultas correlacionadas são executadas para cada linha da consulta. Por outro lado, subconsultas não correlacionadas não fazem referência à consulta externa e são executadas uma vez para toda a consulta.

Scalar subqueries return a single value as result, otherwise the subquery is non-scalar.

Os padrões a seguir são baseados nessas categorias.

Amostra de padrões da origem

Dados de configuração

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

Subconsultas escalares correlacionadas

O Snowflake avalia as subconsultas correlacionadas no tempo de compilação para determinar se são escalares e, portanto, válidas no contexto em que um único valor de retorno é esperado. Para resolver isso, a função de agregação ANY_VALUE é adicionada à coluna retornada quando o resultado não é uma função de agregação. Isto permite que o compilador determine que um único valor de retorno é esperado. Como espera-se que as subconsultas escalares retornem um único valor, a função ANY_VALUE não mudará o resultado, apenas retornará o valor original como está.

Teradata

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

Resultados

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

Resultados

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

Subconsultas escalares não correlacionadas

O Snowflake oferece suporte total a subconsultas escalares não correlacionadas.

Teradata

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

Resultados

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

Snowflake

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

Resultados

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

Subconsultas não escalares

Subconsultas não escalares especificadas dentro de operadores de subconsulta (ANY/ALL/IN/EXISTS) são compatíveis.

Subconsultas não escalares usadas como tabelas derivadas também são aceitas.

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;

Resultado

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

Resultados

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

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

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

Problemas conhecidos

1. Subconsultas com FETCH primeiro que não são escalares não correlacionadas

A Oracle permite utilizar a cláusula FETCH em subconsultas. O Snowflake só permite o uso desta cláusula se a subconsulta for escalar não correlacionada, caso contrário, uma exceção será gerada.

O SnowConvert AI marcará qualquer uso inválido de FETCH em subconsultas com 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);