Expression lists

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

Output Code:

Snowflake

OUT -> Redshift_01.sql

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

Result

Comparisons

Input Code:

Redshift

IN -> Redshift_02.sql

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

Result

Output Code:

Snowflake

OUT -> Redshift_02.sql

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

Result

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

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

Nested tuples

Input Code:

Redshift

IN -> Redshift_03.sql

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

Result

Output Code

Snowflake

OUT -> Redshift_03.sql

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

Result

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

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

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

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

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

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

Known Issues

No issues were found.

Related EWIs

There are no known issues.