SnowConvert: Redshift SELECT INTO Statement¶
SELECT INTO¶
Description¶
Returns rows from tables, views, and user-defined functions and inserts them into a new table. (Redshift SQL Language Reference SELECT statement)
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 ]
For more information please refer to each of the following links:
FROM clause¶
Description¶
The FROM
clause in a query lists the table references (tables, views, and subqueries) that data is selected from. If multiple table references are listed, the tables must be joined, using appropriate syntax in either the FROM
clause or the WHERE
clause. If no join criteria are specified, the system processes the query as a cross-join. (Redshift SQL Language Reference FROM Clause)
Warning
The FROM clause is partially supported in Snowflake. Object unpivoting is not currently supported.
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 ]
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);
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;
EMPLOYEE_NAME |
DEPARTMENT_NAME |
---|---|
John |
HR |
Jorge |
Sales |
Kwaku |
Sales |
Liu |
Sales |
Mateo |
Engineering |
Nikki |
Marketing |
Paulo |
Marketing |
Richard |
Marketing |
Sofía |
Engineering |
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 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;
EMPLOYEE_NAME | DEPARTMENT_NAME |
---|---|
John | HR |
Jorge | Sales |
Kwaku | Sales |
Liu | Sales |
Mateo | Engineering |
Nikki | Marketing |
Paulo | Marketing |
Richard | Marketing |
Sofía | Engineering |
Known Issues¶
There are no known issues.
GROUP BY clause¶
Description¶
The GROUP BY
clause identifies the grouping columns for the query. Grouping columns must be declared when the query computes aggregates with standard functions such as SUM
, AVG
, and COUNT
. (Redshift SQL Language Reference GROUP BY Clause)
The GROUP BY clause is fully supported in Snowflake.
Grammar Syntax
GROUP BY expression [, ...]
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;
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;
MANAGER_ID |
TOTAL_EMPLOYEES |
---|---|
100 |
1 |
101 |
3 |
102 |
2 |
103 |
3 |
104 |
3 |
1 |
Known Issues
There are no known issues.
Related EWIs.
There are no related EWIs.
HAVING clause
Description
The HAVING
clause applies a condition to the intermediate grouped result set that a query returns. (Redshift SQL Language Reference HAVING Clause)
The HAVING clause is fully supported in Snowflake.
Grammar Syntax¶
[ HAVING condition ]
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
HAVING COUNT(id) > 2
ORDER BY manager_id;
MANAGER_ID |
TOTAL_EMPLOYEES |
---|---|
101 |
3 |
103 |
3 |
104 |
3 |
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
HAVING COUNT(id) > 2
ORDER BY manager_id;
MANAGER_ID |
TOTAL_EMPLOYEES |
---|---|
101 |
3 |
103 |
3 |
104 |
3 |
Known Issues¶
There are no known issues.
Related EWIs.¶
There are no related EWIs.
LIMIT and OFFSET clauses¶
Description¶
The LIMIT and OFFSET clauses retrieves and skips the number of rows specified in the number.
The LIMIT and OFFSET clauses are fully supported in Snowflake.
Grammar Syntax
[ LIMIT { number | ALL } ]
[ OFFSET start ]
Sample Source Patterns
LIMIT number
Input Code:
SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT 5;
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;
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;
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;
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 without LIMIT
Snowflake doesn’t support OFFSET without LIMIT. The LIMIT is added after transformation with NULL, which is the default LIMIT.
Input Code:
SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
OFFSET 5;
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;
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
There are no known issues.
Related EWIs.
There are no related EWIs.
Local Variables and Parameters
Description
Redshift also allow to SELECT INTO variables when the statement is executed inside stored procedures.
This pattern is fully supported in Snowflake.
Grammar Syntax¶
SELECT [ select_expressions ] INTO target [ select_expressions ] FROM ...;
Sample Source Patterns¶
SELECT INTO with expressions at the left¶
Input Code:¶
CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
var1 int;
BEGIN
select 10, 100 into param1, var1;
END;
$$ LANGUAGE plpgsql;
param1 |
---|
10 |
Output Code:¶
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;
$$;
TEST_SP1 |
---|
{ “param1”: 10 } |
SELECT INTO with expressions at the right¶
Input Code:¶
CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
var1 int;
BEGIN
select into param1, var1 10, 100;
END;
$$ LANGUAGE plpgsql;
param1 |
---|
10 |
Output Code:¶
Since Snowflake doesn’t support this grammar for SELECT INTO, the expressions are moved to the left of the 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;
$$;
TEST_SP1 |
---|
{ “param1”: 10 } |
Known Issues¶
There are no known issues.
Related EWIs.¶
There are no related EWIs.
ORDER BY clause¶
Description¶
The ORDER BY
clause sorts the result set of a query. (Redshift SQL Language Reference Order By Clause)
The ORDER BY clause is fully supported in Snowflake.
Grammar Syntax
[ ORDER BY expression [ ASC | DESC ] ]
[ NULLS FIRST | NULLS LAST ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
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;
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;
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
There are no known issues.
Related EWIs.
There are no related EWIs.
SELECT list
Description
The SELECT list names the columns, functions, and expressions that you want the query to return. The list represents the output of the query. (Redshift SQL Language Reference SELECT list)
The query start options are fully supported in Snowflake. Just keep in mind that in Snowflake the DISTINCT
and ALL
options must go at the beginning of the query.
Note
In Redshift, if your application allows foreign keys or invalid primary keys, it can cause queries to return incorrect results. For example, a SELECT DISTINCT query could return duplicate rows if the primary key column does not contain all unique values. (Redshift SQL Language Reference SELECT list)
Grammar Syntax¶
SELECT
[ TOP number ]
[ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...]
Sample Source Patterns¶
Top clause¶
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 TOP 5 id, name, manager_id
INTO top_employees
FROM employee;
SELECT * FROM top_employees;
ID |
NAME |
MANAGER_ID |
---|---|---|
100 |
Carlos |
null |
101 |
John |
100 |
102 |
Jorge |
101 |
103 |
Kwaku |
101 |
110 |
Liu |
101 |
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 top_employees AS
SELECT TOP 5 id, name, manager_id
FROM
employee;
SELECT * FROM
top_employees;
ID |
NAME |
MANAGER_ID |
---|---|---|
100 |
Carlos |
null |
101 |
John |
100 |
102 |
Jorge |
101 |
103 |
Kwaku |
101 |
110 |
Liu |
101 |
ALL¶
Input Code:¶
SELECT ALL manager_id
INTO manager
FROM employee;
MANAGER_ID |
---|
null |
100 |
101 |
101 |
101 |
102 |
103 |
103 |
103 |
104 |
104 |
102 |
104 |
Output Code:¶
CREATE TABLE IF NOT EXISTS manager AS
SELECT ALL manager_id
FROM
employee;
MANAGER_ID |
---|
null |
100 |
101 |
101 |
101 |
102 |
103 |
103 |
103 |
104 |
104 |
102 |
104 |
DISTINCT¶
Input Code:¶
SELECT DISTINCT manager_id
INTO manager
FROM employee;
MANAGER_ID |
---|
null |
100 |
101 |
102 |
103 |
104 |
Output Code:¶
CREATE TABLE IF NOT EXISTS manager AS
SELECT DISTINCT manager_id
FROM
employee;
MANAGER_ID |
---|
null |
100 |
101 |
102 |
103 |
104 |
Known Issues¶
There are no known issues.
Related EWIs.¶
There are no related EWIs.
UNION, INTERSECT, and EXCEPT¶
Description¶
The UNION
, INTERSECT
, and EXCEPT
set operators are used to compare and merge the results of two separate query expressions. (Redshift SQL Language Reference Set Operators)
Set operators are fully supported in Snowflake.
Grammar Syntax
query
{ UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query
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;
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;
ID |
NAME |
MANAGER_ID |
---|---|---|
102 |
Jorge |
101 |
103 |
Kwaku |
101 |
110 |
Liu |
101 |
106 |
Mateo |
102 |
201 |
Sofía |
102 |
Known Issues
There are no known issues.
Related EWIs.
There are no related EWIs.
WHERE clause
Description
The WHERE
clause contains conditions that either join tables or apply predicates to columns in tables. (Redshift SQL Language Reference WHERE Clause)
The WHERE clause is fully supported in Snowflake.
Grammar Syntax¶
[ WHERE condition ]
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 id, name, manager_id
INTO employee_names
FROM employee
WHERE name LIKE 'J%';
ID |
NAME |
MANAGER_ID |
---|---|---|
101 |
John |
100 |
102 |
Jorge |
101 |
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 employee_names AS
SELECT id, name, manager_id
FROM
employee
WHERE name LIKE 'J%' ESCAPE '\\';
ID |
NAME |
MANAGER_ID |
---|---|---|
101 |
John |
100 |
102 |
Jorge |
101 |
Known Issues¶
There are no known issues.
Related EWIs.¶
There are no related EWIs.
WITH clause¶
Description¶
A WITH
clause is an optional clause that precedes the SELECT INTO in a query. The WITH
clause defines one or more common_table_expressions. Each common table expression (CTE) defines a temporary table, which is similar to a view definition. You can reference these temporary tables in the FROM
clause. (Redshift SQL Language Reference WITH Clause)
The WITH clause is fully supported in 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 )
Sample Source Patterns
Non-Recursive form
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;
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;
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 |
Recursive form
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;
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;
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
There are no known issues.
Related EWIs.
There are no related EWIs.