SnowConvert AI - Redshift - 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:

  1. WITH clause
  2. SELECT list
  3. FROM clause
  4. WHERE clause
  5. CONNECT BY clause
  6. GROUP BY clause
  7. HAVING clause
  8. QUALIFY clause
  9. UNION, INTERSECT, and EXCEPT
  10. ORDER BY clause
  11. LIMIT and OFFSET clauses
  12. Local Variables and Parameters

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:

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_NAMEDEPARTMENT_NAME
JohnHR
JorgeSales
KwakuSales
LiuSales
MateoEngineering
NikkiMarketing
PauloMarketing
RichardMarketing
SofíaEngineering
Output Code:
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_NAMEDEPARTMENT_NAME
JohnHR
JorgeSales
KwakuSales
LiuSales
MateoEngineering
NikkiMarketing
PauloMarketing
RichardMarketing
SofíaEngineering

Known Issues

There are no known issues.

See SELECT transformation for related EWIs.

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)

Note

:class: tip The GROUP BY clause is fully supported in Snowflake.

Grammar Syntax


 GROUP BY expression [, ...]

Sample Source Patterns

Input Code:

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_IDTOTAL_EMPLOYEES
1001
1013
1022
1033
1043
1
Output Code:
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_IDTOTAL_EMPLOYEES
1001
1013
1022
1033
1043
1

Known Issues

There are no known issues.

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)

Note

:class: tip The HAVING clause is fully supported in Snowflake.

Grammar Syntax


 [ HAVING condition ]

Sample Source Patterns

Input Code:

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_IDTOTAL_EMPLOYEES
1013
1033
1043
Output Code:
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_IDTOTAL_EMPLOYEES
1013
1033
1043

Known Issues

There are no known issues.

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.

Note

:class: tip The LIMIT and OFFSET clauses are fully supported in Snowflake.

Grammar Syntax


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

Sample Source Patterns

LIMIT number

Input Code:
Redshift

 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT 5;
Results
IDNAMEMANAGER_IDSALARY
100Carlos120000.00
101John10090000.00
102Jorge10195000.00
103Kwaku101105000.00
104Paulo102110000.00
Output Code:
Snowflake

 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT 5;
Results
IDNAMEMANAGER_IDSALARY
100Carlos120000.00
101John10090000.00
102Jorge10195000.00
103Kwaku101105000.00
104Paulo102110000.00

LIMIT ALL

Input Code:
Redshift

 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT ALL;
Results
IDNAMEMANAGER_IDSALARY
100Carlos120000.00
101John10090000.00
102Jorge10195000.00
103Kwaku101105000.00
104Paulo102110000.00
105Richard10285000.00
106Mateo10395000.00
107Liu103108000.00
108Zhang10495000.00
Output Code:
Snowflake

 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL;
Results
IDNAMEMANAGER_IDSALARY
100Carlos120000.00
101John10090000.00
102Jorge10195000.00
103Kwaku101105000.00
104Paulo102110000.00
105Richard10285000.00
106Mateo10395000.00
107Liu103108000.00
108Zhang10495000.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:
Redshift

 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
OFFSET 5;
Results
IDNAMEMANAGER_IDSALARY
105Richard10285000.00
106Mateo10395000.00
107Liu103108000.00
108Zhang10495000.00
Output Code:
Snowflake

 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL
OFFSET 5;
Results
IDNAMEMANAGER_IDSALARY
105Richard10285000.00
106Mateo10395000.00
107Liu103108000.00
108Zhang10495000.00

Known Issues

There are no known issues.

There are no related EWIs.

Local Variables and Parameters

Description

Redshift also allows SELECT INTO variables when the statement is executed inside stored procedures.

Note

:class: tip 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:
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
Output Code:
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 with expressions at the right

Input Code:
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
Output Code:

Since Snowflake doesn’t support this grammar for SELECT INTO, the expressions are moved to the left of the 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 }

Known Issues

There are no known issues.

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)

Note

:class: tip 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:

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
IDNAMEMANAGER_IDSALARY
107Liu103108000.00
103Kwaku101105000.00
102Jorge10195000.00
106Mateo10395000.00
108Zhang10495000.00
Output Code:
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
IDNAMEMANAGER_IDSALARY
107Liu103108000.00
103Kwaku101105000.00
102Jorge10195000.00
106Mateo10395000.00
108Zhang10495000.00

Known Issues

There are no known issues.

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)

Note

:class: tip 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:
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
IDNAMEMANAGER_ID
100Carlosnull
101John100
102Jorge101
103Kwaku101
110Liu101
Output Code:
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
IDNAMEMANAGER_ID
100Carlosnull
101John100
102Jorge101
103Kwaku101
110Liu101

ALL

Input Code:
Redshift
:force:
SELECT ALL manager_id
INTO manager
FROM employee;
Results
MANAGER_ID
null
100
101
101
101
102
103
103
103
104
104
102
104
Output Code:
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

Input Code:
Redshift
:force:
SELECT DISTINCT manager_id
INTO manager
FROM employee;
Results
MANAGER_ID
null
100
101
102
103
104
Output Code:
Snowflake

 CREATE TABLE IF NOT EXISTS manager AS
SELECT DISTINCT manager_id
FROM
employee;
Results
MANAGER_ID
null
100
101
102
103
104

Known Issues

There are no known issues.

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)

Note

:class: tip Set operators are fully supported in Snowflake.

Grammar Syntax


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

Sample Source Patterns

Input Code:

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
IDNAMEMANAGER_ID
103Kwaku101
110Liu101
102Jorge101
106Mateo102
201Sofía102
Output Code:
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
IDNAMEMANAGER_ID
102Jorge101
103Kwaku101
110Liu101
106Mateo102
201Sofía102

Known Issues

There are no known issues.

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)

Note

:class: tip The WHERE clause is fully supported in Snowflake.

Grammar Syntax


 [ WHERE condition ]

Sample Source Patterns

Input Code:

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
IDNAMEMANAGER_ID
101John100
102Jorge101
Output Code:
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
IDNAMEMANAGER_ID
101John100
102Jorge101

Known Issues

There are no known issues.

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)

Note

:class: tip 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:
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_IDCUSTOMER_IDORDER_DATETOTAL_AMOUNT
21022024-02-02600.00
41042024-02-04750.00
51052024-02-05900.00
Output Code:
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_IDCUSTOMER_IDORDER_DATETOTAL_AMOUNT
21022024-02-02600.00
41042024-02-04750.00
51052024-02-05900.00

Recursive form

Input Code:
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
IDNAMEMANAGER_ID
101John100
103Kwaku101
102Jorge101
110Liu101
106Mateo102
201Sofía102
105Richard103
110Nikki103
104Paulo103
120Saanvi104
200Shirley104
205Zhang104
Output Code:
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
IDNAMEMANAGER_ID
101John100
103Kwaku101
102Jorge101
110Liu101
106Mateo102
201Sofía102
105Richard103
110Nikki103
104Paulo103
120Saanvi104
200Shirley104
205Zhang104

Known Issues

There are no known issues.

There are no related EWIs.