| 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 *) |
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 *) |
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 |
