IN
Description
An IN condition tests a value for membership in a set of values or in a subquery. (Redshift SQL Language Reference IN condition)
Grammar Syntax
expression [ NOT ] IN (expr_list | table_subquery)
This function is fully supported by Snowflake.
Sample Source Patterns
Setup Table
CREATE TABLE sales (
id INTEGER IDENTITY(1,1),
price FLOAT,
saleDate DATE
);
INSERT INTO sales (price, saleDate) VALUES
(5000, '12/19/2024'),
(4000, '12/18/2024'),
(2000, '12/17/2024'),
(1000, '11/11/2024'),
(7000, '10/10/2024'),
(7000, '05/12/2024');
CREATE TABLE InTest (
col1 Varchar(20) COLLATE CASE_INSENSITIVE,
col2 Varchar(30) COLLATE CASE_SENSITIVE,
d1 date,
num integer,
idx integer);
INSERT INTO InTest values ('A', 'A', ('2012-03-02'), 4,6);
INSERT INTO InTest values ('a', 'a', ('2014-01-02'), 41,7);
CREATE TABLE InTest (
id INTEGER IDENTITY(1,1) ORDER,
price FLOAT,
saleDate DATE
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "01/09/2025", "domain": "test" }}';
INSERT INTO InTest (price, saleDate) VALUES
(5000, '12/19/2024'),
(4000, '12/18/2024'),
(2000, '12/17/2024'),
(1000, '11/11/2024'),
(7000, '10/10/2024'),
(7000, '05/12/2024');
CREATE TABLE InTest (
col1 Varchar(20) COLLATE 'en-ci',
col2 Varchar(30) COLLATE 'en-cs',
d1 date,
num integer,
idx integer)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "01/16/2025", "domain": "test" }}';
INSERT INTO InTest
values ('A', 'A', ('2012-03-02'), 4,6);
INSERT INTO InTest
values ('a', 'a', ('2014-01-02'), 41,7);
Input Code:
IN -> Redshift_01.sql
SELECT * FROM sales
WHERE id IN (2,3);
SELECT 5 IN (
SELECT id FROM sales
WHERE price = 7000
) AS ValidId;
select t.col1 in ('a ','b','c') as r1, t.col2 in ('a ','b','c') as r2 from InTest t order by t.idx;
ID
PRICE
SALEDATE
2
4000
2024-12-18
3
2000
2024-12-17
VALIDID
TRUE
R1
R2
TRUE
FALSE
TRUE
TRUE
Output Code:
OUT -> Redshift_01.sql
SELECT * FROM
sales
WHERE id IN (2,3);
SELECT 5 IN (
SELECT id FROM
sales
WHERE price = 7000
) AS ValidId;
select t.col1 in (RTRIM('a '), RTRIM('b'), RTRIM('c')) as r1, t.col2 in (RTRIM('a '), RTRIM('b'), RTRIM('c')) as r2 from
InTest t order by t.idx;
ID
PRICE
SALEDATE
2
4000
2024-12-18
3
2000
2024-12-17
VALIDID
TRUE
R1
R2
TRUE
FALSE
TRUE
TRUE
Related EWIs
No related EWIs.
Known Issues
No issues were found.
Last updated