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 ]
Pour plus d’informations, veuillez vous référer à chacun des liens suivants :
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 clauseFROM
ou la clauseWHERE
. 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 ]
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;
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;
Results¶
EMPLOYEE_NAME | DEPARTMENT_NAME |
---|---|
John | HR |
Jorge | Sales |
Kwaku | Sales |
Liu | Sales |
Mateo | Engineering |
Nikki | Marketing |
Paulo | Marketing |
Richard | Marketing |
Sofía | Engineering |
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 queSUM
,AVG
, etCOUNT
. (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 [, ...]
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;
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;
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 ]
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;
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;
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 ]
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;
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;
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;
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;
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;
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;
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 ...;
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;
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;
$$;
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;
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;
$$;
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 ]
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;
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;
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 ] [, ...]
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;
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;
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;
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;
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;
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;
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
etEXCEPT
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
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;
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;
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 ]
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%';
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 '\\';
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 clauseWITH
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 clauseFROM
. (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 )
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;
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;
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;
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;
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é.