SnowflakeDocumentation Home

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

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:

IN -> Redshift_01.sql
SELECT *
FROM table3
WHERE quantity IN (1, 5, 10);

Output Code:

OUT -> Redshift_01.sql
SELECT *
FROM
    table3
WHERE quantity IN (1, 5, 10);

Comparisons

Input Code:

IN -> Redshift_02.sql
SELECT *
FROM table3
WHERE (quantity, fruit) = (1, 'apple');

Output Code:

OUT -> Redshift_02.sql
SELECT *
FROM
    table3
WHERE (quantity, fruit) = (1, 'apple');

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:

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;

Output Code:

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;

Nested tuples

Input Code:

IN -> Redshift_03.sql
SELECT *
FROM table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'));

Output Code

OUT -> Redshift_03.sql
SELECT *
FROM
    table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'));

Case statement

Input Code:

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

Output Code

OUT -> Redshift_04.sql
SELECT
    CASE
        WHEN quantity IN (1, 5, 10) THEN 'Found'
        ELSE 'Not Found'
    END AS result
FROM
    table3;

Multiple Expressions

Input Code:

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

Output Code

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

Joins

Input Code:

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

Output Code

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

Known Issues

No issues were found.

Related EWIs

There are no known issues.

PreviousArithmetic operatorsNextConditions

Last updated