Comparison Condition

Conditions

Description

Comparison conditions state logical relationships between two values. All comparison conditions are binary operators with a Boolean return type.

(RedShift SQL Language Reference Comparison Condition)

Grammar Syntax

Redshift supports the comparison operators described in the following table:

Operator	Syntax	Description
<	a < b	Value a is less than value b.
>	a > b	Value a is greater than value b.
<=	a <= b	Value a is less than or equal to value b.
>=	a >= b	Value a is greater than or equal to value b.
=	a = b	Value a is equal to value b.
<> | !=	a <> b | a != b	Value a is not equal to value b.
ANY | SOME	a = ANY(subquery)	Value a is equal to any value returned by the subquery.
ALL	a <> ALL or != ALL (subquery)	Value a is not equal to any value returned by the subquery.
IS TRUE | FALSE | UNKNOWN	a IS TRUE	Value a is Boolean TRUE.

Use of comparison operators on Strings

It is important to note that in Redshift, comparison operators on strings ignore trailing blank spaces. To replicate this behavior in Snowflake, the transformation applies the RTRIM function to remove trailing spaces, ensuring equivalent functionality. For more information: Significance of trailing blanks

Conversion Table

Most of the operators are directly supported by Snowflake; however, the following operators require transformation:

Redshift	Snowflake	Comments
(expression) IS TRUE	expression	Condition is TRUE.
(expression) IS FALSE	NOT (expression)	Condition is FALSE.
(expression) IS UNKNOWN	expression IS NULL	Expression evaluates to NULL (same as UNKNOWN).

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE example_data (
    id INT,
    value INT,
    status BOOLEAN,
    category VARCHAR(10)
);

INSERT INTO example_data (id, value, status, category) VALUES
(1, 50, TRUE, 'A'),
(2, 30, FALSE, 'B'),
(3, 40, NULL, 'C'),
(4, 70, TRUE, 'A '),
(5, 60, FALSE, 'B');

SELECT *
FROM example_data
WHERE value < 60 AND value > 40;

SELECT *
FROM example_data
WHERE value <= 60 AND value >= 40;

SELECT *
FROM example_data
WHERE category = 'A';

SELECT *
FROM example_data
WHERE category != 'A' AND category <> 'B';

SELECT *
FROM example_data
WHERE category = ANY(SELECT category FROM example_data WHERE value > 60); --SOME

SELECT *
FROM example_data
WHERE value <> ALL (SELECT value FROM example_data WHERE status = TRUE);

SELECT *
FROM example_data
WHERE status IS TRUE;

SELECT *
FROM example_data
WHERE status IS FALSE;

SELECT *
FROM example_data
WHERE status IS UNKNOWN;

Results

id	value	status	category
1	50	true	A

id	value	status	category
1	50	true	A
3	40	null	C
5	60	false	B

id	value	status	category
1	50	true	A
4	70	true	A

id	value	status	category
3	40	null	C

id	value	status	category
1	50	true	A
4	70	true	A

id	value	status	category
2	30	false	B
4	40	null	C
5	60	false	B

id	value	status	category
1	50	true	A
4	70	true	A

id	value	status	category
2	30	false	B
5	60	false	B

id	value	status	category
4	40	null	C

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE example_data (
    id INT,
    value INT,
    status BOOLEAN,
    category VARCHAR(10)
);

INSERT INTO example_data (id, value, status, category) VALUES
(1, 50, TRUE, 'A'),
(2, 30, FALSE, 'B'),
(3, 40, NULL, 'C'),
(4, 70, TRUE, 'A '),
(5, 60, FALSE, 'B');

SELECT *
FROM example_data
WHERE value < 60 AND value > 40;

SELECT *
FROM example_data
WHERE value <= 60 AND value >= 40;

SELECT *
FROM example_data
WHERE RTRIM(category) = RTRIM('A ');

SELECT *
FROM example_data
WHERE RTRIM( category) != RTRIM( 'A') AND RTRIM( category) <> RTRIM( 'B');

SELECT *
FROM example_data
WHERE category = ANY(SELECT category FROM example_data WHERE value > 60); --SOME

SELECT *
FROM example_data
WHERE value <> ALL (SELECT value FROM example_data WHERE status = TRUE);

SELECT *
FROM example_data
WHERE status;

SELECT *
FROM example_data
WHERE NOT status;

SELECT *
FROM example_data
WHERE status IS NULL;

Results

id	value	status	category
1	50	true	A

id	value	status	category
1	50	true	A
3	40	null	C
5	60	false	B

id	value	status	category
1	50	true	A
4	70	true	A

id	value	status	category
3	40	null	C

id	value	status	category
1	50	true	A
4	70	true	A

id	value	status	category
2	30	false	B
4	40	null	C
5	60	false	B

id	value	status	category
1	50	true	A
4	70	true	A

id	value	status	category
2	30	false	B
5	60	false	B

id	value	status	category
4	40	null	C

Known Issues

No issues were found.

Related EWIs

There are no known issues.