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 ]
Copy

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 ]
Copy

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;
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

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;
Copy
Results¶
EMPLOYEE_NAMEDEPARTMENT_NAME
JohnHR
JorgeSales
KwakuSales
LiuSales
MateoEngineering
NikkiMarketing
PauloMarketing
RichardMarketing
SofĂ­aEngineering

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)

Note

The GROUP BY clause is fully supported in Snowflake.

Grammar Syntax¶

 GROUP BY expression [, ...]
Copy

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;
Copy
Results¶

MANAGER_ID

TOTAL_EMPLOYEES

100

1

101

3

102

2

103

3

104

3

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;
Copy
Results¶

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)

Note

The HAVING clause is fully supported in Snowflake.

Grammar Syntax¶

 [ HAVING condition ]
Copy

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;
Copy
Results¶

MANAGER_ID

TOTAL_EMPLOYEES

101

3

103

3

104

3

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;
Copy
Results¶

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.

Note

The LIMIT and OFFSET clauses are fully supported in Snowflake.

Grammar Syntax¶

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

Sample Source Patterns¶

LIMIT number¶

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

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

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

Output Code:¶
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 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;
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

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

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.

Note

This pattern is fully supported in Snowflake.

Grammar Syntax¶

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

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;
Copy
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;
$$;
Copy
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;
Copy
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;
$$;
Copy
Results¶

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)

Note

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 ]
Copy

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;
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

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;
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

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)

Note

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 ] [, ...]
Copy

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;
Copy
Results¶

ID

NAME

MANAGER_ID

100

Carlos

null

101

John

100

102

Jorge

101

103

Kwaku

101

110

Liu

101

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;
Copy
Results¶

ID

NAME

MANAGER_ID

100

Carlos

null

101

John

100

102

Jorge

101

103

Kwaku

101

110

Liu

101

ALL¶

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

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

Input Code:¶
Redshift¶
SELECT DISTINCT manager_id
INTO manager
FROM employee;
Copy
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;
Copy
Results¶

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)

Note

Set operators are fully supported in Snowflake.

Grammar Syntax¶

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

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;
Copy
Results¶

ID

NAME

MANAGER_ID

103

Kwaku

101

110

Liu

101

102

Jorge

101

106

Mateo

102

201

SofĂ­a

102

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;
Copy
Results¶

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)

Note

The WHERE clause is fully supported in Snowflake.

Grammar Syntax¶

 [ WHERE condition ]
Copy

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%';
Copy
Results¶

ID

NAME

MANAGER_ID

101

John

100

102

Jorge

101

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 '\\';
Copy
Results¶

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)

Note

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 )
Copy

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;
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

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;
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

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;
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

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;
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

Known Issues¶

There are no known issues.

Related EWIs.¶

There are no related EWIs.