When this behavior is not suitable, use the IS DISTINCT FROM predicates: a IS DISTINCT FROM b a IS NOT DISTINCT FROM bįor non-null inputs, IS DISTINCT FROM is the same as the operator. For example, 7 = NULL yields null, as does 7 NULL.
Ordinary comparison operators yield null (signifying “ unknown”), not true or false, when either input is null. If you need to write a more complex sub-expression in BETWEEN, write parentheses around the sub-expression. To resolve this, only a limited set of expression types are allowed as the second argument of a BETWEEN clause.
The use of AND in the BETWEEN syntax creates an ambiguity with the use of AND as a logical operator. The various variants of BETWEEN are implemented in terms of the ordinary comparison operators, and therefore will work for any data type(s) that can be compared. If it is not, those two arguments are automatically swapped, so that a nonempty range is always implied. BETWEEN SYMMETRIC is like BETWEEN except there is no requirement that the argument to the left of AND be less than or equal to the argument on the right. Notice that BETWEEN treats the endpoint values as included in the range. The BETWEEN predicate simplifies range tests: a BETWEEN x AND y NULL::boolean IS NOT UNKNOWN → f (rather than NULL) Test whether boolean expression yields true or false. NULL::boolean IS UNKNOWN → t (rather than NULL) Test whether boolean expression yields unknown. NULL::boolean IS NOT FALSE → t (rather than NULL) Test whether boolean expression yields true or unknown. NULL::boolean IS FALSE → f (rather than NULL) Test whether boolean expression yields false. NULL::boolean IS NOT TRUE → t (rather than NULL) Test whether boolean expression yields false or unknown. NULL::boolean IS TRUE → f (rather than NULL) Test whether boolean expression yields true. Test whether value is not null (nonstandard syntax). Test whether value is null (nonstandard syntax). NULL IS NOT DISTINCT FROM NULL → t (rather than NULL) NULL IS DISTINCT FROM NULL → f (rather than NULL)ĭatatype IS NOT DISTINCT FROM datatype → booleanĮqual, treating null as a comparable value.ġ IS NOT DISTINCT FROM NULL → f (rather than NULL) Not equal, treating null as a comparable value.ġ IS DISTINCT FROM NULL → t (rather than NULL) Not between, after sorting the two endpoint values.ĭatatype IS DISTINCT FROM datatype → boolean These behave much like operators, but have special syntax mandated by the SQL standard.ĭatatype BETWEEN datatype AND datatype → booleanīetween (inclusive of the range endpoints).ĭatatype NOT BETWEEN datatype AND datatype → booleanĭatatype BETWEEN SYMMETRIC datatype AND datatype → booleanīetween, after sorting the two endpoint values.ĭatatype NOT BETWEEN SYMMETRIC datatype AND datatype → boolean There are also some comparison predicates, as shown in Table 9.2. Use the BETWEEN predicates shown below to perform range tests. Thus, expressions like 1 < 2 < 3 are not valid (because there is no < operator to compare a Boolean value with 3). Some cases of this sort are implemented directly by “ cross-type” comparison operators, but if no such operator is available, the parser will coerce the less-general type to the more-general type and apply the latter's comparison operator.Īs shown above, all comparison operators are binary operators that return values of type boolean. It is usually possible to compare values of related data types as well for example integer > bigint will work. In addition, arrays, composite types, and ranges can be compared if their component data types are comparable. These comparison operators are available for all built-in data types that have a natural ordering, including numeric, string, and date/time types. Hence, it is not possible to implement != and operators that do different things. != is an alias, which is converted to at a very early stage of parsing. Is the standard SQL notation for “ not equal”.
0 kommentar(er)
