SnowConvert: Instrução Redshift SELECT INTO¶

SELECT INTO¶

Descrição¶

Retorna linhas de tabelas, exibições e funções definidas pelo usuário e as insere em uma nova tabela. (Instrução SELECT de referência de linguagem Redshift SQL)

Sintaxe da gramática¶

 [ WITH with_subquery [, ...] ]
SELECT
[ TOP number ] [ ALL | DISTINCT ]
* | expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM table_reference [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | { EXCEPT | MINUS } } [ ALL ] query ]
[ ORDER BY expression
[ ASC | DESC ]
[ LIMIT { number | ALL } ]
[ OFFSET start ]

Copy

Para obter mais informações, consulte cada um dos links a seguir:

Cláusula WITH
Lista SELECT
Cláusula FROM
Cláusula WHERE
Cláusula CONNECT BY
Cláusula GROUP BY
Cláusula HAVING
Cláusula QUALIFY
UNION, INTERSECT e EXCEPT
Cláusula ORDER BY
Cláusulas LIMIT e OFFSET
Variáveis locais e parâmetros

Cláusula FROM¶

Descrição¶

A cláusula FROM em uma consulta lista as referências de tabela (tabelas, exibições e subconsultas) das quais os dados são selecionados. Se várias referências de tabela forem listadas, as tabelas deverão ser unidas, usando a sintaxe apropriada na cláusula FROM ou na cláusula WHERE. Se nenhum critério de junção for especificado, o sistema processará a consulta como uma junção cruzada. (Cláusula FROM de referência de linguagem Redshift SQL)

Aviso

A cláusula FROMé parcialmente suportada no Snowflake. A desvinculação de objetos não é suportada no momento.

Sintaxe da gramática¶

 FROM table_reference [, ...]

<table_reference> ::=
with_subquery_table_name [ table_alias ]
table_name [ * ] [ table_alias ]
( subquery ) [ table_alias ]
table_reference [ NATURAL ] join_type table_reference
   [ ON join_condition | USING ( join_column [, ...] ) ]
table_reference PIVOT ( 
   aggregate(expr) [ [ AS ] aggregate_alias ]
   FOR column_name IN ( expression [ AS ] in_alias [, ...] )
) [ table_alias ]
table_reference UNPIVOT [ INCLUDE NULLS | EXCLUDE NULLS ] ( 
   value_column_name 
   FOR name_column_name IN ( column_reference [ [ AS ]
   in_alias ] [, ...] )
) [ table_alias ]
UNPIVOT expression AS value_alias [ AT attribute_alias ]

Copy

Amostra de padrões da origem¶

Código de entrada:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
);
  
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

CREATE TABLE department (
    id INT,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO department(id, name, manager_id) VALUES
(1, 'HR', 100),
(2, 'Sales', 101),
(3, 'Engineering', 102),
(4, 'Marketing', 103);

SELECT e.name AS employee_name, d.name AS department_name
INTO employees_in_department
FROM employee e
INNER JOIN department d ON e.manager_id = d.manager_id;

Copy

EMPLOYEE_NAME	DEPARTMENT_NAME
John	HR
Jorge	Vendas
Kwaku	Vendas
Liu	Vendas
Mateo	Engenharia
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Sofía	Engenharia

Código de saída:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/06/2025",  "domain": "test" }}';

INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

CREATE TABLE department (
    id INT,
    name VARCHAR(50),
    manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/06/2025",  "domain": "test" }}';

INSERT INTO department (id, name, manager_id) VALUES
(1, 'HR', 100),
(2, 'Sales', 101),
(3, 'Engineering', 102),
(4, 'Marketing', 103);

CREATE TABLE IF NOT EXISTS employees_in_department AS
  SELECT e.name AS employee_name, d.name AS department_name
  FROM
    employee e
  INNER JOIN
      department d ON e.manager_id = d.manager_id;

Copy

EMPLOYEE_NAME	DEPARTMENT_NAME
John	HR
Jorge	Sales
Kwaku	Sales
Liu	Sales
Mateo	Engineering
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Sofía	Engineering

Problemas conhecidos¶

Não há problemas conhecidos.

Cláusula GROUP BY¶

Descrição¶

A cláusula GROUP BY identifica as colunas de agrupamento da consulta. As colunas de agrupamento devem ser declaradas quando a consulta calcula os agregados com funções padrão, como SUM, AVG e COUNT. (Cláusula GROUP BY de referência de linguagem Redshift SQL)

A cláusula GROUP BYé totalmente compatível com o Snowflake.

Grammar Syntax

 GROUP BY expression [, ...]

Copy

Sample Source Patterns

Input Code:

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
);
  
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

SELECT 
    manager_id,
    COUNT(id) AS total_employees
INTO manager_employees
FROM employee
GROUP BY manager_id
ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
	1

Output Code:

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/06/2025",  "domain": "test" }}';

INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

CREATE TABLE IF NOT EXISTS manager_employees AS
  SELECT
      manager_id,
      COUNT(id) AS total_employees
  FROM
      employee
  GROUP BY manager_id
  ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
	1

Known Issues

Não há problemas conhecidos.

Related EWIs.

Não há EWIs relacionados.

Cláusula HAVING

Description

A cláusula HAVING aplica uma condição ao conjunto de resultados agrupados intermediários que uma consulta retorna. (Cláusula HAVING de referência de linguagem Redshift SQL)

A cláusulaHAVING é totalmente compatível com o Snowflake.

Sintaxe da gramática¶

 [ HAVING condition ]

Copy

Amostra de padrões da origem¶

Código de entrada:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
);
  
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

SELECT manager_id, COUNT(id) AS total_employees
INTO manager_employees
FROM
employee
GROUP BY manager_id
HAVING COUNT(id) > 2
ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
101	3
103	3
104	3

Código de saída:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/06/2025",  "domain": "test" }}';

INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

CREATE TABLE IF NOT EXISTS manager_employees AS
  SELECT manager_id, COUNT(id) AS total_employees
  FROM
    employee
  GROUP BY manager_id
  HAVING COUNT(id) > 2
  ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
101	3
103	3
104	3

Problemas conhecidos¶

Não há problemas conhecidos.

EWIs Relacionados.¶

Não há EWIs relacionados.

Cláusulas LIMIT e OFFSET¶

Descrição¶

As cláusulas LIMIT e OFFSET recuperam e pulam o número de linhas especificadas no número.

As cláusulas LIMIT e OFFSET são totalmente compatíveis com o Snowflake.

Grammar Syntax

 [ LIMIT { number | ALL } ]
[ OFFSET start ]

Copy

Sample Source Patterns

Número LIMIT

Input Code:

 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT 5;

Copy

ID	NAME	MANAGER_ID	SALARY
100	Carlos		120000,00
101	John	100	90000,00
102	Jorge	101	95000,00
103	Kwaku	101	105000,00
104	Paulo	102	110000,00

Output Code:

 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT 5;

Copy

ID	NAME	MANAGER_ID	SALARY
100	Carlos		120000,00
101	John	100	90000,00
102	Jorge	101	95000,00
103	Kwaku	101	105000,00
104	Paulo	102	110000,00

LIMIT ALL

Input Code:

 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT ALL;

Copy

ID	NAME	MANAGER_ID	SALARY
100	Carlos		120000,00
101	John	100	90000,00
102	Jorge	101	95000,00
103	Kwaku	101	105000,00
104	Paulo	102	110000,00
105	Richard	102	85000,00
106	Mateo	103	95000,00
107	Liu	103	108000,00
108	Zhang	104	95000,00

Output Code:

 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL;

Copy

ID	NAME	MANAGER_ID	SALARY
100	Carlos		120000,00
101	John	100	90000,00
102	Jorge	101	95000,00
103	Kwaku	101	105000,00
104	Paulo	102	110000,00
105	Richard	102	85000,00
106	Mateo	103	95000,00
107	Liu	103	108000,00
108	Zhang	104	95000,00

OFFSET sem LIMIT

O Snowflake não é compatível com OFFSET sem LIMIT. O LIMIT é adicionado após a transformação com NULL, que é o LIMIT padrão.

Input Code:

 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
OFFSET 5;

Copy

ID	NAME	MANAGER_ID	SALARY
105	Richard	102	85000,00
106	Mateo	103	95000,00
107	Liu	103	108000,00
108	Zhang	104	95000,00

Output Code:

 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL
OFFSET 5;

Copy

ID	NAME	MANAGER_ID	SALARY
105	Richard	102	85000,00
106	Mateo	103	95000,00
107	Liu	103	108000,00
108	Zhang	104	95000,00

Known Issues

Não há problemas conhecidos.

Related EWIs.

Não há EWIs relacionados.

Variáveis locais e parâmetros

Description

O Redshift também permite variáveis SELECT INTO quando a instrução é executada dentro de procedimentos armazenados.

Esse padrão é totalmente compatível com o Snowflake.

Sintaxe da gramática¶

 SELECT [ select_expressions ] INTO target [ select_expressions ] FROM ...;

Copy

Amostra de padrões da origem¶

SELECT INTO com expressões à esquerda¶

Código de entrada:¶

 CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
    var1 int;
BEGIN
     select 10, 100 into param1, var1;
END;
$$ LANGUAGE plpgsql;

Copy

param1
10

Código de saída:¶

 CREATE OR REPLACE PROCEDURE test_sp1 (param1 int)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
        DECLARE
            var1 int;
BEGIN
     select 10, 100 into
                : param1,
                : var1;
            RETURN OBJECT_CONSTRUCT('param1', :param1);
END;
$$;

Copy

TEST_SP1
{ «param1»: 10 }

SELECT INTO com expressões à direita¶

Código de entrada:¶

 CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
    var1 int;
BEGIN
     select into param1, var1 10, 100;
END;
$$ LANGUAGE plpgsql;

Copy

param1
10

Código de saída:¶

Como o Snowflake não oferece suporte a essa gramática para SELECT INTO, as expressões são movidas para a esquerda de INTO.

 CREATE OR REPLACE PROCEDURE test_sp1 (param1 int)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
        DECLARE
            var1 int;
BEGIN
     select
                10, 100
            into
                : param1,
                : var1;
            RETURN OBJECT_CONSTRUCT('param1', :param1);
END;
$$;

Copy

TEST_SP1
{ «param1»: 10 }

Problemas conhecidos¶

Não há problemas conhecidos.

EWIs Relacionados.¶

Não há EWIs relacionados.

Cláusula ORDER BY¶

Descrição¶

A cláusula ORDER BY classifica o conjunto de resultados de uma consulta. (Cláusula Order By de referência de linguagem Redshift SQL)

A cláusulaORDER BY é totalmente compatível com o Snowflake.

Grammar Syntax

 [ ORDER BY expression [ ASC | DESC ] ]
[ NULLS FIRST | NULLS LAST ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]

Copy

Sample Source Patterns

Input Code:

 CREATE TABLE employee (
    id INT,
    name VARCHAR(20),
    manager_id INT,
    salary DECIMAL(10, 2)
);

INSERT INTO employee (id, name, manager_id, salary) VALUES
(100, 'Carlos', NULL, 120000.00),
(101, 'John', 100, 90000.00),
(102, 'Jorge', 101, 95000.00),
(103, 'Kwaku', 101, 105000.00),
(104, 'Paulo', 102, 110000.00),
(105, 'Richard', 102, 85000.00),
(106, 'Mateo', 103, 95000.00),
(107, 'Liu', 103, 108000.00),
(108, 'Zhang', 104, 95000.00);

SELECT id, name, manager_id, salary
INTO salaries
FROM employee
ORDER BY salary DESC NULLS LAST, name ASC NULLS FIRST
LIMIT 5                                        
OFFSET 2;

Copy

ID	NAME	MANAGER_ID	SALARY
107	Liu	103	108000,00
103	Kwaku	101	105000,00
102	Jorge	101	95000,00
106	Mateo	103	95000,00
108	Zhang	104	95000,00

Output Code:

 CREATE TABLE employee (
    id INT,
    name VARCHAR(20),
    manager_id INT,
    salary DECIMAL(10, 2)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/06/2025",  "domain": "test" }}';

INSERT INTO employee (id, name, manager_id, salary) VALUES
(100, 'Carlos', NULL, 120000.00),
(101, 'John', 100, 90000.00),
(102, 'Jorge', 101, 95000.00),
(103, 'Kwaku', 101, 105000.00),
(104, 'Paulo', 102, 110000.00),
(105, 'Richard', 102, 85000.00),
(106, 'Mateo', 103, 95000.00),
(107, 'Liu', 103, 108000.00),
(108, 'Zhang', 104, 95000.00);

CREATE TABLE IF NOT EXISTS salaries AS
    SELECT id, name, manager_id, salary
    FROM
        employee
    ORDER BY salary DESC NULLS LAST, name ASC NULLS FIRST
    LIMIT 5
    OFFSET 2;

Copy

ID	NAME	MANAGER_ID	SALARY
107	Liu	103	108000,00
103	Kwaku	101	105000,00
102	Jorge	101	95000,00
106	Mateo	103	95000,00
108	Zhang	104	95000,00

Known Issues

Não há problemas conhecidos.

Related EWIs.

Não há EWIs relacionados.

Lista SELECT

Description

A lista SELECT nomeia as colunas, funções e expressões que você deseja que a consulta retorne. A lista representa o resultado da consulta. (Lista SELECT de referência de linguagem Redshift SQL)

As opções de início de consulta são totalmente compatíveis com o Snowflake. Lembre-se de que, no Snowflake, as opções DISTINCT e ALL devem estar no início da consulta.

Nota

No Redshift, se seu aplicativo permitir chaves estrangeiras ou chaves primárias inválidas, isso pode fazer com que as consultas retornem resultados incorretos. Por exemplo, uma consulta SELECT DISTINCT poderia retornar linhas duplicadas se a coluna de chave primária não contivesse todos os valores exclusivos. (Lista SELECT de referência de linguagem Redshift SQL)

Sintaxe da gramática¶

 SELECT
[ TOP number ]
[ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...]

Copy

Amostra de padrões da origem¶

Cláusula superior¶

Código de entrada:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
);
  
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
  
SELECT TOP 5 id, name, manager_id 
INTO top_employees
FROM employee;

SELECT * FROM top_employees;

Copy

ID	NAME	MANAGER_ID
100	Carlos	nulo
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

Código de saída:¶

 CREATE TABLE employee
(
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/06/2025",  "domain": "test" }}';

INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

CREATE TABLE IF NOT EXISTS top_employees AS
SELECT TOP 5 id, name, manager_id
  FROM
    employee;

SELECT * FROM
  top_employees;

Copy

ID	NAME	MANAGER_ID
100	Carlos	nulo
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

ALL¶

Código de entrada:¶

SELECT ALL manager_id
INTO manager
FROM employee;

Copy

MANAGER_ID
nulo
100
101
101
101
102
103
103
103
104
104
102
104

Código de saída:¶

 CREATE TABLE IF NOT EXISTS manager AS
SELECT ALL manager_id
FROM
employee;

Copy

MANAGER_ID
nulo
100
101
101
101
102
103
103
103
104
104
102
104

DISTINCT¶

Código de entrada:¶

SELECT DISTINCT manager_id
INTO manager
FROM employee;

Copy

MANAGER_ID
nulo
100
101
102
103
104

Código de saída:¶

 CREATE TABLE IF NOT EXISTS manager AS
SELECT DISTINCT manager_id
FROM
employee;

Copy

MANAGER_ID
nulo
100
101
102
103
104

Problemas conhecidos¶

Não há problemas conhecidos.

EWIs Relacionados.¶

Não há EWIs relacionados.

UNION, INTERSECT e EXCEPT¶

Descrição¶

Os set operators UNION, INTERSECT e EXCEPT são usados para comparar e mesclar os resultados de duas expressões de consulta separadas. (Set Operators de referência de linguagem Redshift SQL)

Os operadores de conjunto são totalmente compatíveis com o Snowflake.

Grammar Syntax

 query
{ UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query

Copy

Sample Source Patterns

Input Code:

 SELECT id, name, manager_id
INTO some_employees
FROM
employee
WHERE manager_id = 101

UNION

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 102

UNION ALL

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101

INTERSECT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 103

EXCEPT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 104;

Copy

ID	NAME	MANAGER_ID
103	Kwaku	101
110	Liu	101
102	Jorge	101
106	Mateo	102
201	Sofía	102

Output Code:

 CREATE TABLE IF NOT EXISTS some_employees AS
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101

UNION

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 102

UNION ALL

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101

INTERSECT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 103

EXCEPT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 104;

Copy

ID	NAME	MANAGER_ID
102	Jorge	101
103	Kwaku	101
110	Liu	101
106	Mateo	102
201	Sofía	102

Known Issues

Não há problemas conhecidos.

Related EWIs.

Não há EWIs relacionados.

Cláusula WHERE

Description

A cláusula WHERE contém condições que unem tabelas ou aplicam predicados a colunas em tabelas. (Cláusula WHERE de referência de linguagem Redshift SQL)

A cláusula WHEREé totalmente compatível com o Snowflake.

Sintaxe da gramática¶

 [ WHERE condition ]

Copy

Amostra de padrões da origem¶

Código de entrada:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
);
  
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

SELECT id, name, manager_id
INTO employee_names
FROM employee
WHERE name LIKE 'J%';

Copy

ID	NAME	MANAGER_ID
101	John	100
102	Jorge	101

Código de saída:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/06/2025",  "domain": "test" }}';

INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

CREATE TABLE IF NOT EXISTS employee_names AS
  SELECT id, name, manager_id
  FROM
    employee
  WHERE name LIKE 'J%' ESCAPE '\\';

Copy

ID	NAME	MANAGER_ID
101	John	100
102	Jorge	101

Problemas conhecidos¶

Não há problemas conhecidos.

EWIs Relacionados.¶

Não há EWIs relacionados.

Cláusula WITH¶

Descrição¶

Uma cláusula WITH é uma cláusula opcional que precede SELECT INTO em uma consulta. A cláusula WITH define uma ou mais common_table_expressions. Cada expressão de tabela comum (CTE) define uma tabela temporária, que é semelhante a uma definição de visualização. Você pode fazer referência a essas tabelas temporárias na cláusula FROM. (Cláusula WITH de referência de linguagem Redshift SQL)

A cláusulaWITH é totalmente compatível com o Snowflake.

Grammar Syntax

 [ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]

--Where common_table_expression can be either non-recursive or recursive. 
--Following is the non-recursive form:
CTE_table_name [ ( column_name [, ...] ) ] AS ( query )

--Following is the recursive form of common_table_expression:
CTE_table_name (column_name [, ...] ) AS ( recursive_query )

Copy

Sample Source Patterns

Forma não recursiva

Input Code:

 CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
);


INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES
(1, 101, '2024-02-01', 250.00),
(2, 102, '2024-02-02', 600.00),
(3, 103, '2024-02-03', 150.00),
(4, 104, '2024-02-04', 750.00),
(5, 105, '2024-02-05', 900.00);


WITH HighValueOrders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        total_amount
    FROM orders
    WHERE total_amount > 500
)
SELECT * INTO high_value_orders FROM HighValueOrders;

SELECT * FROM high_value_orders;

Copy

ORDER_ID	CUSTOMER_ID	ORDER_DATE	TOTAL_AMOUNT
2	102	2024-02-02	600,00
4	104	2024-02-04	750,00
5	105	2024-02-05	900,00

Output Code:

 CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}';


INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES
(1, 101, '2024-02-01', 250.00),
(2, 102, '2024-02-02', 600.00),
(3, 103, '2024-02-03', 150.00),
(4, 104, '2024-02-04', 750.00),
(5, 105, '2024-02-05', 900.00);

CREATE TABLE IF NOT EXISTS high_value_orders AS
WITH HighValueOrders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        total_amount
    FROM
        orders
    WHERE total_amount > 500
    )
    SELECT *
    FROM
    HighValueOrders;
    
SELECT * FROM
    high_value_orders;

Copy

ORDER_ID	CUSTOMER_ID	ORDER_DATE	TOTAL_AMOUNT
2	102	2024-02-02	600,00
4	104	2024-02-04	750,00
5	105	2024-02-05	900,00

Forma recursiva

Input Code:

 CREATE TABLE employee (
   id INT,
   name VARCHAR(20),
   manager_id INT
);

INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);


WITH RECURSIVE john_org(id, name, manager_id, level) 
AS
( 
   SELECT id, name, manager_id, 1 AS level
   FROM employee
   WHERE name = 'John'
   UNION ALL
   SELECT e.id, e.name, e.manager_id, level + 1 AS next_level
   FROM employee e, john_org j
   WHERE e.manager_id = j.id and level < 4
)
SELECT DISTINCT id, name, manager_id into new_org FROM john_org ORDER BY manager_id;

SELECT * FROM new_org;

Copy

ID	NAME	MANAGER_ID
101	John	100
103	Kwaku	101
102	Jorge	101
110	Liu	101
106	Mateo	102
201	Sofía	102
105	Richard	103
110	Nikki	103
104	Paulo	103
120	Saanvi	104
200	Shirley	104
205	Zhang	104

Output Code:

 CREATE TABLE employee (
   id INT,
   name VARCHAR(20),
   manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/06/2025",  "domain": "test" }}';

INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

CREATE TABLE IF NOT EXISTS new_org AS
WITH RECURSIVE john_org(id, name, manager_id, level)
AS
(
   SELECT id, name, manager_id, 1 AS level
   FROM
         employee
   WHERE
         RTRIM( name) = RTRIM( 'John')
   UNION ALL
   SELECT e.id, e.name, e.manager_id, level + 1 AS next_level
   FROM
         employee e,
         john_org j
   WHERE e.manager_id = j.id and level < 4
   )
   SELECT DISTINCT id, name, manager_id
   FROM
   john_org
   ORDER BY manager_id;

   
SELECT * FROM
   new_org;

Copy

ID	NAME	MANAGER_ID
101	John	100
103	Kwaku	101
102	Jorge	101
110	Liu	101
106	Mateo	102
201	Sofía	102
105	Richard	103
110	Nikki	103
104	Paulo	103
120	Saanvi	104
200	Shirley	104
205	Zhang	104

Known Issues

Não há problemas conhecidos.

Related EWIs.

Não há EWIs relacionados.