Comparison operators

value1 = value2

Equals

SELECT 1 = 0 :  false

SELECT 4 = 4 :  true

value1 <> value2

Not equals

SELECT 1 <> 0 :  true

SELECT 4 <> 4 :  false

value1 > value2

Greater than

SELECT 1 > 0 :  true

SELECT 4 > 4 :  false

value1 >= value2

Greater than or equal

SELECT 1 >= 0 :  true

SELECT 4 >= 4 :  true

value1 < value2

Lower than

SELECT 1 < 0 :  false

SELECT 4 < 4 :  false

value1 <= value2

Less than or equal

SELECT 1 <= 0 :  false

SELECT 4 <= 4 :  true

value IS NULL

Whether value is null

SELECT 1 IS NULL :  false

SELECT NULL IS NULL :  true

value IS NOT NULL

Whether value is not null

SELECT 1 IS NOT NULL :  true

SELECT NULL IS NOT NULL :  false

value1 IS DISTINCT FROM value2

Whether two values are not equal, treating null values as the same

SELECT 1 IS DISTINCT FROM 1 :  false

SELECT 1 IS DISTINCT FROM 4 :  true

SELECT 1 IS DISTINCT FROM NULL :  true

SELECT NULL IS DISTINCT FROM NULL :  false

value1 IS NOT DISTINCT FROM value2

Whether two values are equal, treating null values as the same

SELECT 1 IS NOT DISTINCT FROM 1 :  true

SELECT 1 IS NOT DISTINCT FROM 4 :  false

SELECT 1 IS NOT DISTINCT FROM NULL :  false

SELECT NULL IS NOT DISTINCT FROM NULL :  true

value1 BETWEEN value2 AND value3

Whether value1 is greater than or equal to value2 and less than or equal to value3

SELECT 4 BETWEEN 3 AND 10 :  true

SELECT 1 BETWEEN 3 AND 10 :  false

value1 NOT BETWEEN value2 AND value3

Whether value1 is greater than or equal to value2 and less than or equal to value3

SELECT 4 NOT BETWEEN 3 AND 10 :  false

SELECT 1 NOT BETWEEN 3 AND 10 :  true

string1 LIKE string2

Whether string1 matches pattern string2. The wildcard '%' represents zero, one or multiple characters. The wildcard '_' represents one single character. Use 'ESCAPE' to define a escape character (if you want to escape a wildcard). To find if string1 starts with a sequence, use pattern 'sequence%' (analogously use '%sequence' for ends with). The matching is case sensitive. To perform a case insensitive matching, use UPPER (or LOWER) on string1 and pattern.

SELECT name FROM employee WHERE name LIKE 'S%' :  Smith, Saunders

SELECT name FROM employee WHERE UPPER(name) LIKE 'SM%' :  Smith

SELECT name FROM employee WHERE name LIKE '_m%' :  Smith

SELECT 'abcd%' LIKE '%!%' ESCAPE '!' :  true

string1 NOT LIKE string2

Whether string1 does not match pattern string2. The wildcard '%' represents zero, one or multiple characters. The wildcard '_' represents one single character. Use 'ESCAPE' to define a escape character (if you want to escape a wildcard). To find if string1 does not start with a sequence, use pattern 'sequence%' (analogously use '%sequence' for ends with). The matching is case sensitive. To perform a case insensitive matching, use UPPER (or LOWER) on string1 and pattern.

SELECT name FROM employee WHERE name NOT LIKE 'S%' :  Hamilton

SELECT name FROM employee WHERE UPPER(name) NOT LIKE 'SM%' :  Hamilton, Saunders

SELECT name FROM employee WHERE name NOT LIKE '_m%' :  Hamilton, Saunders

SELECT 'abcd%' NOT LIKE '%!_' ESCAPE '!' :  true

value IN (value [, value]*)

Whether value is equal to a value in a list

SELECT firstname FROM employee WHERE name IN ('Smith', 'Hamilton') :  John, Maria

value NOT IN (value [, value]*)

Whether value is not equal to every value in a list

SELECT firstname FROM employee WHERE name NOT IN ('Smith', 'Hamilton') :  Jennifer

value IN (sub-query)

Whether value is equal to a row returned by sub-query

SELECT e.firstname FROM employee e WHERE e.department.id IN (SELECT d.id FROM department d WHERE d.name='IT') :  John