Expression lists

Description

An expression list is a combination of expressions, and can appear in membership and comparison conditions (WHERE clauses) and in GROUP BY clauses. (Redshift SQL Language Reference Expression lists).

This syntax is fully supported in Snowflake.

Grammar Syntax

expression , expression , ... | (expression, expression, ...)

Sample Source Patterns

Setup data

Redshift

CREATE TABLE table1 (
    quantity VARCHAR(50),
    fruit VARCHAR(50)
);

CREATE TABLE table2 (
    quantity VARCHAR(50),
    fruit VARCHAR(50)
);

CREATE TABLE table3 (
    id INT,
    name VARCHAR(50),
    quantity INT,
    fruit VARCHAR(50),
    price INT
);

INSERT INTO table1 (quantity, fruit)
VALUES
    ('one', 'apple'),
    ('two', 'banana'),
    ('three', 'cherry');

INSERT INTO table2 (quantity, fruit)
VALUES
    ('one', 'apple'),
    ('two', 'banana'),
    ('four', 'orange');

INSERT INTO table3 (id, name, quantity, fruit, price)
VALUES
    (1, 'Alice', 1, 'apple', 100),
    (2, 'Bob', 5, 'banana', 200),
    (3, 'Charlie', 10, 'cherry', 300),
    (4, 'David', 15, 'orange', 400);

IN Clause

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT *
FROM table3
WHERE quantity IN (1, 5, 10);

Result

ID	NAME	QUANTITY	FRUIT	PRICE
1	Alice	1	apple	100
2	Bob	5	banana	200
3	Charlie	10	cherry	300

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT *
FROM
    table3
WHERE quantity IN (1, 5, 10);

Result

ID	NAME	QUANTITY	FRUIT	PRICE
1	Alice	1	apple	100
2	Bob	5	banana	200
3	Charlie	10	cherry	300

Comparisons

Input Code:

Redshift

IN -> Redshift_02.sql

SELECT *
FROM table3
WHERE (quantity, fruit) = (1, 'apple');

Result

ID	NAME	QUANTITY	FRUIT	PRICE
1	Alice	1	apple	100

Output Code:

Snowflake

OUT -> Redshift_02.sql

SELECT *
FROM
    table3
WHERE (quantity, fruit) = (1, 'apple');

Result

ID	NAME	QUANTITY	FRUIT	PRICE
1	Alice	1	apple	100

Expression list comparisons with the following operators may have a different behavior in Snowflake. ( < , <= , > , >=). These operators are transformed into logical AND operations to achieve full equivalence in Snowflake.

Input Code:

Redshift

IN -> Redshift_02.sql

SELECT (1,8,20) < (2,2,0) as r1,
       (1,null,2) > (1,0,8) as r2,
       (null,null,2) < (1,0,8) as r3,
       (1,0,null) <= (1,1,0) as r4,
       (1,1,0) >= (1,1,20) as r5;

Result

R1	R2	R3	R4	R5
FALSE	FALSE	NULL	NULL	FALSE

Output Code:

Snowflake

OUT -> Redshift_02.sql

SELECT
    (1 < 2
    AND 8 < 2
    AND 20 < 0) as r1,
    (1 > 1
    AND null > 0
    AND 2 > 8) as r2,
    (null < 1
    AND null < 0
    AND 2 < 8) as r3,
    (1 <= 1
    AND 0 <= 1
    AND null <= 0) as r4,
    (1 >= 1
    AND 1 >= 1
    AND 0 >= 20) as r5;

Result

R1	R2	R3	R4	R5
FALSE	FALSE	NULL	NULL	FALSE

Nested tuples

Input Code:

Redshift

IN -> Redshift_03.sql

SELECT *
FROM table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'));

Result

ID	NAME	QUANTITY	FRUIT	PRICE
1	Alice	1	apple	100
2	Bob	5	banana	200
3	Charlie	10	cherry	300

Output Code

Snowflake

OUT -> Redshift_03.sql

SELECT *
FROM
    table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'));

Result

ID	NAME	QUANTITY	FRUIT	PRICE
1	Alice	1	apple	100
2	Bob	5	banana	200
3	Charlie	10	cherry	300

Case statement

Input Code:

Redshift

IN -> Redshift_04.sql

SELECT
    CASE
        WHEN quantity IN (1, 5, 10) THEN 'Found'
        ELSE 'Not Found'
    END AS result
FROM table3;

Result

RESULT
Found
Found
Found
Not Found
Not Found
Not Found

Output Code

Snowflake

OUT -> Redshift_04.sql

SELECT
    CASE
        WHEN quantity IN (1, 5, 10) THEN 'Found'
        ELSE 'Not Found'
    END AS result
FROM
    table3;

Result

RESULT
Found
Found
Found
Not Found
Not Found
Not Found

Multiple Expressions

Input Code:

Redshift

IN -> Redshift_05.sql

SELECT *
FROM table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'))
  AND price IN (100, 200, 300);

Result

ID	NAME	QUANTITY	FRUIT	PRICE
1	Alice	1	apple	100
2	Bob	5	banana	200
3	Charlie	10	cherry	300

Output Code

Snowflake

OUT -> Redshift_05.sql

SELECT *
FROM
    table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'))
  AND price IN (100, 200, 300);

Result

ID	NAME	QUANTITY	FRUIT	PRICE
1	Alice	1	apple	100
2	Bob	5	banana	200
3	Charlie	10	cherry	300

Joins

Input Code:

Redshift

IN -> Redshift_06.sql

SELECT *
FROM table1 t1
JOIN table2 t2
    ON (t1.quantity, t1.fruit) = (t2.quantity, t2.fruit)
WHERE t1.quantity = 'one' AND t1.fruit = 'apple';

Result

QUANTITY	FRUIT	QUANTITY	FRUIT
one	apple	one	apple

Output Code

Snowflake

OUT -> Redshift_06.sql

SELECT *
FROM
table1 t1
JOIN
        table2 t2
    ON (t1.quantity, t1.fruit) = (t2.quantity, t2.fruit)
WHERE t1.quantity = 'one' AND t1.fruit = 'apple';

Result

QUANTITY	FRUIT	QUANTITY	FRUIT
one	apple	one	apple

Known Issues

No issues were found.

Related EWIs

There are no known issues.