SnowConvert AI - Redshift - SELECT INTO¶

설명¶

테이블, 뷰 및 사용자 정의 함수에서 행을 반환하여 새 테이블에 삽입합니다. (Redshift SQL Language Reference SELECT 문)

문법 구문¶

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

자세한 내용은 다음 각 링크를 참조하십시오.

FROM 절¶

설명¶

쿼리의 FROM 절은 데이터가 선택된 테이블 참조(테이블, 뷰 및 하위 쿼리)를 목록으로 표시합니다. 여러 테이블 참조가 목록에 있는 경우 FROM 절 또는 WHERE 절에서 적절한 구문을 사용하여 테이블을 조인해야 합니다. 조인 조건이 지정되지 않으면 시스템은 쿼리를 교차 조인으로 처리합니다. (Redshift SQL Language Reference FROM 절)

경고

FROM 절은 Snowflake에서 부분적으로 지원됩니다. Object unpivoting 은 현재 지원되지 않습니다.

문법 구문¶

 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 ]

샘플 소스 패턴¶

입력 코드:¶

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;

결과¶


EMPLOYEE_NAME	DEPARTMENT_NAME
John	HR
Jorge	Sales
Kwaku	Sales
Liu	Sales
Mateo	Engineering
Nikki	마케팅
Paulo	마케팅
Richard	마케팅
Sofía	Engineering

출력 코드:¶

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;

결과¶

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¶

알려진 문제는 없습니다.

GROUP BY 절¶

설명¶

GROUP BY 절은 쿼리의 그룹화 열을 식별합니다. 그룹화 열은 쿼리가 SUM, AVG, COUNT 같은 표준 함수로 집계를 계산할 때 선언해야 합니다. (Redshift SQL Language Reference GROUP BY 절)

참고

GROUP BY 절은 Snowflake에서 완전히 지원됩니다.

문법 구문¶

 GROUP BY expression [, ...]

샘플 소스 패턴¶

입력 코드:¶

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;

결과¶


MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
	1

출력 코드:¶

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;

결과¶


MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
	1

Known Issues¶

알려진 문제는 없습니다.

HAVING 절¶

설명¶

HAVING 절은 쿼리가 반환하는 중간 그룹화된 결과 세트에 조건을 적용합니다. (Redshift SQL Language Reference HAVING 절)

참고

HAVING 절 은 Snowflake에서 완전히 지원됩니다.

문법 구문¶

 [ HAVING condition ]

샘플 소스 패턴¶

입력 코드:¶

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;

결과¶


MANAGER_ID	TOTAL_EMPLOYEES
101	3
103	3
104	3

출력 코드:¶

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;

결과¶


MANAGER_ID	TOTAL_EMPLOYEES
101	3
103	3
104	3

Known Issues¶

알려진 문제는 없습니다.

LIMIT 및 OFFSET 절¶

설명¶

LIMIT 및 OFFSET 절은 숫자에 지정된 행 수를 검색하고 건너뜁니다.

참고

LIMIT 및 OFFSET 절은 Snowflake에서 완전히 지원됩니다.

문법 구문¶

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

샘플 소스 패턴¶

LIMIT 숫자¶

입력 코드:¶

Redshift¶

 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

출력 코드:¶

Snowflake¶

 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¶

입력 코드:¶

Redshift¶

 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

출력 코드:¶

Snowflake¶

 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

LIMIT을 제외한 OFFSET¶

Snowflake는 LIMIT 가 없는 OFFSET 을 지원하지 않습니다. LIMIT 는 기본값인 NULL 로 변환한 후 추가되며, 이 값이 기본 LIMIT 입니다.

입력 코드:¶

Redshift¶

 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

출력 코드:¶

Snowflake¶

 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¶

알려진 문제는 없습니다.

로컬 변수 및 매개 변수¶

설명¶

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

참고

이 패턴은 Snowflake에서 완벽하게 지원됩니다.

문법 구문¶

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

샘플 소스 패턴¶

왼쪽에 식이 있는 SELECT INTO¶

입력 코드:¶

Redshift¶

 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

출력 코드:¶

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

결과¶


TEST_SP1
{ “param1”: 10 }

오른쪽에 식이 있는 SELECT INTO¶

입력 코드:¶

Redshift¶

 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

출력 코드:¶

Snowflake는 SELECT INTO 에 대해 이 문법을 지원하지 않으므로 식은 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;
$$;

결과¶


TEST_SP1
{ “param1”: 10 }

Known Issues¶

알려진 문제는 없습니다.

ORDER BY 절¶

설명¶

ORDER BY 절은 쿼리 결과 세트를 정렬합니다. (Redshift SQL Language Reference Order By 절)

참고

ORDER BY 절 은 Snowflake에서 완전히 지원됩니다.

문법 구문¶

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

샘플 소스 패턴¶

입력 코드:¶

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;

결과¶


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

출력 코드:¶

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;

결과¶


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¶

알려진 문제는 없습니다.

SELECT 목록¶

설명¶

SELECT 목록은 쿼리에서 반환할 열, 함수 및 식의 이름을 지정합니다. 목록은 쿼리의 출력을 나타냅니다. (Redshift SQL Language Reference SELECT 목록)

참고

쿼리 시작 옵션 은 Snowflake에서 완벽하게 지원됩니다. Snowflake에서는 DISTINCT 및 ALL 옵션이 쿼리 시작 부분에 있어야 한다는 점을 기억하십시오.

참고

애플리케이션에서 외래 키 또는 유효하지 않은 기본 키를 허용하는 경우 Redshift에서 쿼리 결과가 잘못 반환될 수 있습니다. 예를 들어, 기본 키 열에 모든 고유 값이 포함되어 있지 않은 경우 SELECT DISTINCT 쿼리는 중복 행을 반환할 수 있습니다. (Redshift SQL Language Reference SELECT 목록)

문법 구문¶

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

샘플 소스 패턴¶

Top 절¶

입력 코드:¶

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;

결과¶


ID	NAME	MANAGER_ID
100	Carlos	Null
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

출력 코드:¶

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;

결과¶


ID	NAME	MANAGER_ID
100	Carlos	Null
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

ALL¶

입력 코드:¶

Redshift¶

SELECT ALL manager_id
INTO manager
FROM employee;

결과¶


MANAGER_ID
Null
100
101
101
101
102
103
103
103
104
104
102
104

출력 코드:¶

Snowflake¶

 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¶

입력 코드:¶

Redshift¶

SELECT DISTINCT manager_id
INTO manager
FROM employee;

결과¶


MANAGER_ID
Null
100
101
102
103
104

출력 코드:¶

Snowflake¶

 CREATE TABLE IF NOT EXISTS manager AS
SELECT DISTINCT manager_id
FROM
employee;

결과¶


MANAGER_ID
Null
100
101
102
103
104

Known Issues¶

알려진 문제는 없습니다.

UNION, INTERSECT, EXCEPT¶

설명¶

UNION, INTERSECT, EXCEPT _set 연산자_는 2개의 개별 쿼리 식의 결과를 비교하고 병합하는 데 사용됩니다. (Redshift SQL Language Reference Set 연산자)

참고

Set 연산자 는 Snowflake에서 완벽하게 지원됩니다.

문법 구문¶

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

샘플 소스 패턴¶

입력 코드:¶

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;

결과¶


ID	NAME	MANAGER_ID
103	Kwaku	101
110	Liu	101
102	Jorge	101
106	Mateo	102
201	Sofía	102

출력 코드:¶

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;

결과¶


ID	NAME	MANAGER_ID
102	Jorge	101
103	Kwaku	101
110	Liu	101
106	Mateo	102
201	Sofía	102

Known Issues¶

알려진 문제는 없습니다.

WHERE 절¶

설명¶

WHERE 절에는 테이블을 조인하거나 테이블의 열에 조건자를 적용하는 조건이 포함되어 있습니다. (Redshift SQL Language Reference WHERE 절)

참고

WHERE 절은 Snowflake에서 완전히 지원됩니다.

문법 구문¶

 [ WHERE condition ]

샘플 소스 패턴¶

입력 코드:¶

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

결과¶


ID	NAME	MANAGER_ID
101	John	100
102	Jorge	101

출력 코드:¶

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

결과¶


ID	NAME	MANAGER_ID
101	John	100
102	Jorge	101

Known Issues¶

알려진 문제는 없습니다.

WITH 절¶

설명¶

WITH 절은 쿼리에서 SELECT INTO 앞에 오는 선택적 절입니다. WITH 절은 1개 이상의 _common_table_expression_을 정의합니다. 각 공통 테이블 식(CTE)은 뷰 정의와 유사한 임시 테이블을 정의합니다. FROM 절에서 이러한 임시 테이블을 참조할 수 있습니다. (Redshift SQL Language Reference WITH 절)

참고

WITH 절 은 Snowflake에서 완전히 지원됩니다.

문법 구문¶

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

샘플 소스 패턴¶

비재귀 형식¶

입력 코드:¶

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;

결과¶


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

출력 코드:¶

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;

결과¶


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

재귀 형식¶

입력 코드:¶

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;

결과¶


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

출력 코드:¶

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;

결과¶


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¶

알려진 문제는 없습니다.

SnowConvert AI - Redshift - SELECT INTO¶

설명¶

문법 구문¶

FROM 절¶

설명¶

문법 구문¶

샘플 소스 패턴¶

입력 코드:¶

Redshift¶

결과¶

출력 코드:¶

Redshift¶

결과¶

Known Issues¶

관련 EWIs.¶

GROUP BY 절¶

설명¶

문법 구문¶

샘플 소스 패턴¶

입력 코드:¶

Redshift¶

결과¶

출력 코드:¶

Snowflake¶

결과¶

Known Issues¶

관련 EWIs.¶

HAVING 절¶

설명¶

문법 구문¶

샘플 소스 패턴¶

입력 코드:¶

Redshift¶

결과¶

출력 코드:¶

Snowflake¶

결과¶

Known Issues¶

관련 EWIs.¶

LIMIT 및 OFFSET 절¶

설명¶

문법 구문¶

샘플 소스 패턴¶

LIMIT 숫자¶

입력 코드:¶

Redshift¶

결과¶

출력 코드:¶

Snowflake¶

결과¶

LIMIT ALL¶

입력 코드:¶

Redshift¶

결과¶

출력 코드:¶

Snowflake¶

결과¶

LIMIT을 제외한 OFFSET¶

입력 코드:¶

Redshift¶

결과¶

출력 코드:¶

Snowflake¶

결과¶

Known Issues¶

관련 EWIs.¶

로컬 변수 및 매개 변수¶

설명¶

문법 구문¶

샘플 소스 패턴¶

왼쪽에 식이 있는 SELECT INTO¶

입력 코드:¶

Redshift¶

결과¶

출력 코드:¶

Snowflake¶

결과¶

오른쪽에 식이 있는 SELECT INTO¶

입력 코드:¶

Redshift¶