SnowConvert AI - Redshift - SELECT INTO

Description

Renvoie des lignes à partir de tables, de vues et de fonctions définies par l’utilisateur et les insère dans une nouvelle table. (Référence linguistique Redshift SQL instruction SELECT)

Grammar Syntax

 [ 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

Pour plus d’informations, veuillez vous référer à chacun des liens suivants :

  1. WITH clause

  2. SELECT list

  3. FROM clause

  4. WHERE clause

  5. Clause CONNECT BY

  6. GROUP BY clause

  7. HAVING clause

  8. Clause QUALIFY

  9. UNION, INTERSECT, and EXCEPT

  10. ORDER BY clause

  11. Clauses LIMIT et OFFSET clauses

  12. Variables et paramètres locaux

Clause FROM

Description

La clause FROM d’une requête annonce les références des tables (tables, vues et sous-requêtes) à partir desquelles les données sont sélectionnées. Si plusieurs références de tables sont annoncées, les tables doivent être reliées en utilisant la syntaxe appropriée dans la clause FROM ou la clause WHERE. Si aucun critère de jointure n’est spécifié, le système traite la requête comme une jointure croisée. (Référence linguistique Redshift SQL clause FROM)

Avertissement

La clause FROM est partiellement prise en charge dans Snowflake. Le dépivotage d’objet n’est pas pris en charge actuellement.

Grammar Syntax

 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

Modèles d’échantillons de sources

Code d’entrée :

Redshift
 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
Results

EMPLOYEE_NAME

DEPARTMENT_NAME

John

HR

Jorge

Sales

Kwaku

Sales

Liu

Sales

Mateo

Engineering

Nikki

Marketing

Paulo

Marketing

Richard

Marketing

Sofía

Engineering

Code de sortie :
Redshift
 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
Results
EMPLOYEE_NAMEDEPARTMENT_NAME
JohnHR
JorgeSales
KwakuSales
LiuSales
MateoEngineering
NikkiMarketing
PauloMarketing
RichardMarketing
SofíaEngineering

Problèmes connus

Il n’y a pas de problème connu.

Clause GROUP BY

Description

La clause GROUP BY identifie les colonnes de regroupement de la requête. Les colonnes de regroupement doivent être déclarées lorsque la requête calcule des agrégats avec des fonctions standard telles que SUM, AVG, et COUNT. (Référence linguistique Redshift SQL clause GROUP BY)

Note

La clause GROUP BY est entièrement prise en charge dans Snowflake.

Grammar Syntax

 GROUP BY expression [, ...]
Copy

Modèles d’échantillons de sources

Code d’entrée :

Redshift
 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
Results

MANAGER_ID

TOTAL_EMPLOYEES

100

1

101

3

102

2

103

3

104

3

1

Code de sortie :
Snowflake
 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
Results

MANAGER_ID

TOTAL_EMPLOYEES

100

1

101

3

102

2

103

3

104

3

1

Problèmes connus

Il n’y a pas de problème connu.

EWIs connexes.

Il n’y a aucun EWIs associé.

Clause HAVING

Description

La clause HAVING applique une condition au jeu de résultats groupés intermédiaire renvoyé par une requête. (Référence linguistique Redshift SQL clause HAVING)

Note

La clause HAVING est entièrement prise en charge dans Snowflake.

Grammar Syntax

 [ HAVING condition ]
Copy

Modèles d’échantillons de sources

Code d’entrée :

Redshift
 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
Results

MANAGER_ID

TOTAL_EMPLOYEES

101

3

103

3

104

3

Code de sortie :
Snowflake
 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
Results

MANAGER_ID

TOTAL_EMPLOYEES

101

3

103

3

104

3

Problèmes connus

Il n’y a pas de problème connu.

EWIs connexes.

Il n’y a aucun EWIs associé.

Clauses LIMIT et OFFSET

Description

Les clauses LIMIT et OFFSET récupèrent et sautent le nombre de lignes spécifié dans le nombre.

Note

Les clauses LIMIT et OFFSET sont entièrement prises en charge dans Snowflake.

Grammar Syntax

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

Modèles d’échantillons de sources

Nombre LIMIT

Code d’entrée :
Redshift
 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT 5;
Copy
Results

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

Code de sortie :
Snowflake
 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT 5;
Copy
Results

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

Code d’entrée :
Redshift
 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT ALL;
Copy
Results

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

Code de sortie :
Snowflake
 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL;
Copy
Results

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 sans LIMIT

Snowflake ne prend pas en charge OFFSET sans LIMIT. La LIMIT est ajoutée après transformation avec NULL, qui est la LIMIT par défaut.

Code d’entrée :
Redshift
 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
OFFSET 5;
Copy
Results

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

Code de sortie :
Snowflake
 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL
OFFSET 5;
Copy
Results

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

Problèmes connus

Il n’y a pas de problème connu.

EWIs connexes.

Il n’y a aucun EWIs associé.

Variables et paramètres locaux

Description

Redshift permet également d’utiliser des variables SELECT INTO lorsque l’instruction est exécutée à l’intérieur de procédures stockées.

Note

Ce modèle est entièrement pris en charge par Snowflake.

Grammar Syntax

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

Modèles d’échantillons de sources

SELECT INTO avec des expressions à gauche

Code d’entrée :
Redshift
 CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
    var1 int;
BEGIN
     select 10, 100 into param1, var1;
END;
$$ LANGUAGE plpgsql;
Copy
Results

param1

10

Code de sortie :
Snowflake
 CREATE OR REPLACE PROCEDURE test_sp1 (param1 OUT int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
AS $$
        DECLARE
            var1 int;
BEGIN
     select 10, 100 into
                : param1,
                : var1;
END;
$$;
Copy
Results

TEST_SP1

{ « param1 »: 10 }

SELECT INTO avec des expressions à droite

Code d’entrée :
Redshift
 CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
    var1 int;
BEGIN
     select into param1, var1 10, 100;
END;
$$ LANGUAGE plpgsql;
Copy
Results

param1

10

Code de sortie :

Comme Snowflake ne prend pas en charge cette grammaire pour SELECT INTO, les expressions sont déplacées à gauche de INTO.

Snowflake
 CREATE OR REPLACE PROCEDURE test_sp1 (param1 OUT int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
AS $$
        DECLARE
            var1 int;
BEGIN
     select
                10, 100
            into
                : param1,
                : var1;
END;
$$;
Copy
Results

TEST_SP1

{ « param1 »: 10 }

Problèmes connus

Il n’y a pas de problème connu.

EWIs connexes.

Il n’y a aucun EWIs associé.

Clause ORDER BY

Description

La clause ORDER BY permet de trier le jeu de résultats d’une requête. (Référence linguistique Redshift SQL clause Order By)

Note

La clause ORDER BY est entièrement prise en charge dans Snowflake.

Grammar Syntax

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

Modèles d’échantillons de sources

Code d’entrée :

Redshift
 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
Results

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

Code de sortie :
Snowflake
 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
Results

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

Problèmes connus

Il n’y a pas de problème connu.

EWIs connexes.

Il n’y a aucun EWIs associé.

Liste SELECT

Description

La liste SELECT nomme les colonnes, les fonctions et les expressions que vous souhaitez que la requête renvoie. La liste représente la sortie de la requête. (Référence linguistique (Redshift SQL liste SELECT)

Note

Les options de démarrage des requêtes sont entièrement prises en charge dans Snowflake. Gardez à l’esprit que dans Snowflake, les options DISTINCT et ALL doivent être placées au début de la requête.

Note

Dans Redshift, si votre application autorise les clés étrangères ou les clés primaires non valides, cela peut amener les requêtes à renvoyer des résultats incorrects. Par exemple, une requête SELECT DISTINCT pourrait renvoyer des lignes en double si la colonne de clé primaire ne contient pas toutes les valeurs uniques. (Référence linguistique Redshift SQL liste SELECT)

Grammar Syntax

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

Modèles d’échantillons de sources

Clause Top

Code d’entrée :
Redshift
 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
Results

ID

NAME

MANAGER_ID

100

Carlos

null

101

John

100

102

Jorge

101

103

Kwaku

101

110

Liu

101

Code de sortie :
Snowflake
 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
Results

ID

NAME

MANAGER_ID

100

Carlos

null

101

John

100

102

Jorge

101

103

Kwaku

101

110

Liu

101

ALL

Code d’entrée :
Redshift
SELECT ALL manager_id
INTO manager
FROM employee;
Copy
Results

MANAGER_ID

null

100

101

101

101

102

103

103

103

104

104

102

104

Code de sortie :
Snowflake
 CREATE TABLE IF NOT EXISTS manager AS
SELECT ALL manager_id
FROM
employee;
Copy
Results

MANAGER_ID

null

100

101

101

101

102

103

103

103

104

104

102

104

DISTINCT

Code d’entrée :
Redshift
SELECT DISTINCT manager_id
INTO manager
FROM employee;
Copy
Results

MANAGER_ID

null

100

101

102

103

104

Code de sortie :
Snowflake
 CREATE TABLE IF NOT EXISTS manager AS
SELECT DISTINCT manager_id
FROM
employee;
Copy
Results

MANAGER_ID

null

100

101

102

103

104

Problèmes connus

Il n’y a pas de problème connu.

EWIs connexes.

Il n’y a aucun EWIs associé.

UNION, INTERSECT et EXCEPT

Description

Les opérateurs Set UNION, INTERSECT et EXCEPT sont utilisés pour comparer et fusionner les résultats de deux expressions de requête distinctes. (Référence linguistique Redshift SQL opérateurs Set)

Note

Les opérateurs Set sont entièrement pris en charge dans Snowflake.

Grammar Syntax

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

Modèles d’échantillons de sources

Code d’entrée :

Redshift
 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
Results

ID

NAME

MANAGER_ID

103

Kwaku

101

110

Liu

101

102

Jorge

101

106

Mateo

102

201

Sofía

102

Code de sortie :
Snowflake
 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
Results

ID

NAME

MANAGER_ID

102

Jorge

101

103

Kwaku

101

110

Liu

101

106

Mateo

102

201

Sofía

102

Problèmes connus

Il n’y a pas de problème connu.

EWIs connexes.

Il n’y a aucun EWIs associé.

Clause WHERE

Description

La clause WHERE contient des conditions qui joignent des tables ou appliquent des prédicats aux colonnes des tables. (Référence linguistique Redshift SQL clause WHERE )

Note

La clause WHERE est entièrement prise en charge dans Snowflake.

Grammar Syntax

 [ WHERE condition ]
Copy

Modèles d’échantillons de sources

Code d’entrée :

Redshift
 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
Results

ID

NAME

MANAGER_ID

101

John

100

102

Jorge

101

Code de sortie :
Snowflake
 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
Results

ID

NAME

MANAGER_ID

101

John

100

102

Jorge

101

Problèmes connus

Il n’y a pas de problème connu.

EWIs connexes.

Il n’y a aucun EWIs associé.

Clause WITH

Description

Une clause WITH est une clause facultative qui précède la clause SELECT INTO dans une requête. La clause WITH définit une ou plusieurs common_table_expressions. Chaque expression de table commune (CTE) définit une table temporaire, qui est similaire à la définition d’une vue. Vous pouvez faire référence à ces tables temporaires dans la clause FROM. (Référence linguistique Redshift SQL clause WITH)

Note

La clause WITH est entièrement prise en charge dans 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

Modèles d’échantillons de sources

Forme non récursive

Code d’entrée :
Redshift
 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
Results

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

Code de sortie :
Snowflake
 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
Results

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

Forme récursive

Code d’entrée :
Redshift
 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
Results

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

Code de sortie :
Snowflake
 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": "07/11/2025",  "domain": "no-domain-provided" }}';

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 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
   FROM
   john_org
   ORDER BY manager_id;
SELECT * FROM
   new_org;
Copy
Results

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

Problèmes connus

Il n’y a pas de problème connu.

EWIs connexes.

Il n’y a aucun EWIs associé.