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:
SELECT *
FROM table3
WHERE quantity IN (1, 5, 10);
Output Code:
SELECT *
FROM
table3
WHERE quantity IN (1, 5, 10);
Comparisons
Input Code:
SELECT *
FROM table3
WHERE (quantity, fruit) = (1, 'apple');
Output Code:
SELECT *
FROM
table3
WHERE (quantity, fruit) = (1, 'apple');
Input Code:
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:
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:
SELECT *
FROM table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'));
Output Code
SELECT *
FROM
table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'));
Case statement
Input Code:
SELECT
CASE
WHEN quantity IN (1, 5, 10) THEN 'Found'
ELSE 'Not Found'
END AS result
FROM table3;
Output Code
SELECT
CASE
WHEN quantity IN (1, 5, 10) THEN 'Found'
ELSE 'Not Found'
END AS result
FROM
table3;
Multiple Expressions
Input Code:
SELECT *
FROM table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'))
AND price IN (100, 200, 300);
Output Code
SELECT *
FROM
table3
WHERE (quantity, fruit) IN ((1, 'apple'), (5, 'banana'), (10, 'cherry'))
AND price IN (100, 200, 300);
Joins
Input Code:
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
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.
Last updated