An expression list is a combination of expressions, and can appear in membership and comparison conditions (WHERE clauses) and in GROUP BY clauses. ().
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);
ID
NAME
QUANTITY
FRUIT
PRICE
1
Alice
1
apple
100
2
Bob
5
banana
200
3
Charlie
10
cherry
300
Output Code:
OUT -> Redshift_01.sql
SELECT *
FROM
table3
WHERE quantity IN (1, 5, 10);
ID
NAME
QUANTITY
FRUIT
PRICE
1
Alice
1
apple
100
2
Bob
5
banana
200
3
Charlie
10
cherry
300
Comparisons
Input Code:
IN -> Redshift_02.sql
SELECT *
FROM table3
WHERE (quantity, fruit) = (1, 'apple');
ID
NAME
QUANTITY
FRUIT
PRICE
1
Alice
1
apple
100
Output Code:
OUT -> Redshift_02.sql
SELECT *
FROM
table3
WHERE (quantity, fruit) = (1, 'apple');
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:
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;
R1
R2
R3
R4
R5
FALSE
FALSE
NULL
NULL
FALSE
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;
R1
R2
R3
R4
R5
FALSE
FALSE
NULL
NULL
FALSE
Nested tuples
Input Code:
IN -> Redshift_03.sql
SELECT *
FROM table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'));
ID
NAME
QUANTITY
FRUIT
PRICE
1
Alice
1
apple
100
2
Bob
5
banana
200
3
Charlie
10
cherry
300
Output Code
OUT -> Redshift_03.sql
SELECT *
FROM
table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'));
ID
NAME
QUANTITY
FRUIT
PRICE
1
Alice
1
apple
100
2
Bob
5
banana
200
3
Charlie
10
cherry
300
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;
RESULT
Found
Found
Found
Not Found
Not Found
Not Found
Output Code
OUT -> Redshift_04.sql
SELECT
CASE
WHEN quantity IN (1, 5, 10) THEN 'Found'
ELSE 'Not Found'
END AS result
FROM
table3;
RESULT
Found
Found
Found
Not Found
Not Found
Not Found
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);
ID
NAME
QUANTITY
FRUIT
PRICE
1
Alice
1
apple
100
2
Bob
5
banana
200
3
Charlie
10
cherry
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);
ID
NAME
QUANTITY
FRUIT
PRICE
1
Alice
1
apple
100
2
Bob
5
banana
200
3
Charlie
10
cherry
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';
QUANTITY
FRUIT
QUANTITY
FRUIT
one
apple
one
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';
