Comparison operators¶
A comparison operator tests the relationship between
two values and returns a corresponding value of true
, false
, or
NULL
.
Table of contents
Basic operators¶
For simple data types, the following basic operators can be used:
Operator |
Description |
---|---|
|
Less than |
|
Greater than |
|
Less than or equal to |
|
Greater than or equal to |
|
Equal |
|
Not equal |
|
Not equal (same as |
When comparing strings, a lexicographical comparison is performed:
cr> select name from locations where name > 'Argabuthon' order by name;
+------------------------------------+
| name |
+------------------------------------+
| Arkintoofle Minor |
| Bartledan |
| Galactic Sector QQ7 Active J Gamma |
| North West Ripple |
| Outer Eastern Rim |
+------------------------------------+
SELECT 5 rows in set (... sec)
When comparing dates, ISO date formats can be used:
cr> select date, position from locations where date <= '1979-10-12' and
... position < 3 order by position;
+--------------+----------+
| date | position |
+--------------+----------+
| 308534400000 | 1 |
| 308534400000 | 2 |
+--------------+----------+
SELECT 2 rows in set (... sec)
When comparing Geo Shapes, topological comparison is used. Topological equality means that the geometries have the same dimension, and their point-sets occupy the same space. This means that the order of vertices may be different in topologically equal geometries:
cr> SELECT 'POLYGON (( 0 0, 1 0, 1 1, 0 1, 0 0))'::GEO_SHAPE = 'POLYGON (( 1 0, 1 1, 0 1, 0 0, 1 0))'::GEO_SHAPE as res;
+------+
| res |
+------+
| TRUE |
+------+
SELECT 1 row in set (... sec)
Geometry collections, containing only linestrings, points or polygons are normalized to MultiLineString, MultiPoint and MultiPolygon. Hence, geometry collection of points is equal to a MultiPoint with the same points set:
cr> select 'MULTIPOINT ((10 40), (40 30), (20 20))'::GEO_SHAPE = 'GEOMETRYCOLLECTION (POINT (10 40), POINT(40 30), POINT(20 20))'::GEO_SHAPE;
+------+
| true |
+------+
| TRUE |
+------+
SELECT 1 row in set (... sec)
Tip
Comparison operators are commonly used to filter rows (e.g., in the WHERE and HAVING clauses of a SELECT statement). However, basic comparison operators can be used as value expressions in any context. For example:
cr> SELECT 1 < 10 as my_column;
+-----------+
| my_column |
+-----------+
| TRUE |
+-----------+
SELECT 1 row in set (... sec)
WHERE
clause operators¶
Within a WHERE clause, the following operators can also be used:
Operator |
Description |
---|---|
|
|
Matches a part of the given value |
|
Negates a condition |
|
Matches a null value |
|
Matches a non-null value |
|
|
True if IP is within the given IP range (using CIDR notation) |
|
Shortcut for |
See also