diff options
Diffstat (limited to 'doc/src/sgml/func/func-comparisons.sgml')
| -rw-r--r-- | doc/src/sgml/func/func-comparisons.sgml | 336 |
1 files changed, 336 insertions, 0 deletions
diff --git a/doc/src/sgml/func/func-comparisons.sgml b/doc/src/sgml/func/func-comparisons.sgml new file mode 100644 index 00000000000..6a6e0bd4019 --- /dev/null +++ b/doc/src/sgml/func/func-comparisons.sgml @@ -0,0 +1,336 @@ + <sect1 id="functions-comparisons"> + <title>Row and Array Comparisons</title> + + <indexterm> + <primary>IN</primary> + </indexterm> + + <indexterm> + <primary>NOT IN</primary> + </indexterm> + + <indexterm> + <primary>ANY</primary> + </indexterm> + + <indexterm> + <primary>ALL</primary> + </indexterm> + + <indexterm> + <primary>SOME</primary> + </indexterm> + + <indexterm> + <primary>composite type</primary> + <secondary>comparison</secondary> + </indexterm> + + <indexterm> + <primary>row-wise comparison</primary> + </indexterm> + + <indexterm> + <primary>comparison</primary> + <secondary>composite type</secondary> + </indexterm> + + <indexterm> + <primary>comparison</primary> + <secondary>row constructor</secondary> + </indexterm> + + <indexterm> + <primary>IS DISTINCT FROM</primary> + </indexterm> + + <indexterm> + <primary>IS NOT DISTINCT FROM</primary> + </indexterm> + + <para> + This section describes several specialized constructs for making + multiple comparisons between groups of values. These forms are + syntactically related to the subquery forms of the previous section, + but do not involve subqueries. + The forms involving array subexpressions are + <productname>PostgreSQL</productname> extensions; the rest are + <acronym>SQL</acronym>-compliant. + All of the expression forms documented in this section return + Boolean (true/false) results. + </para> + + <sect2 id="functions-comparisons-in-scalar"> + <title><literal>IN</literal></title> + +<synopsis> +<replaceable>expression</replaceable> IN (<replaceable>value</replaceable> <optional>, ...</optional>) +</synopsis> + + <para> + The right-hand side is a parenthesized list + of expressions. The result is <quote>true</quote> if the left-hand expression's + result is equal to any of the right-hand expressions. This is a shorthand + notation for + +<synopsis> +<replaceable>expression</replaceable> = <replaceable>value1</replaceable> +OR +<replaceable>expression</replaceable> = <replaceable>value2</replaceable> +OR +... +</synopsis> + </para> + + <para> + Note that if the left-hand expression yields null, or if there are + no equal right-hand values and at least one right-hand expression yields + null, the result of the <token>IN</token> construct will be null, not false. + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + </para> + </sect2> + + <sect2 id="functions-comparisons-not-in"> + <title><literal>NOT IN</literal></title> + +<synopsis> +<replaceable>expression</replaceable> NOT IN (<replaceable>value</replaceable> <optional>, ...</optional>) +</synopsis> + + <para> + The right-hand side is a parenthesized list + of expressions. The result is <quote>true</quote> if the left-hand expression's + result is unequal to all of the right-hand expressions. This is a shorthand + notation for + +<synopsis> +<replaceable>expression</replaceable> <> <replaceable>value1</replaceable> +AND +<replaceable>expression</replaceable> <> <replaceable>value2</replaceable> +AND +... +</synopsis> + </para> + + <para> + Note that if the left-hand expression yields null, or if there are + no equal right-hand values and at least one right-hand expression yields + null, the result of the <token>NOT IN</token> construct will be null, not true + as one might naively expect. + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + </para> + + <tip> + <para> + <literal>x NOT IN y</literal> is equivalent to <literal>NOT (x IN y)</literal> in all + cases. However, null values are much more likely to trip up the novice when + working with <token>NOT IN</token> than when working with <token>IN</token>. + It is best to express your condition positively if possible. + </para> + </tip> + </sect2> + + <sect2 id="functions-comparisons-any-some"> + <title><literal>ANY</literal>/<literal>SOME</literal> (array)</title> + +<synopsis> +<replaceable>expression</replaceable> <replaceable>operator</replaceable> ANY (<replaceable>array expression</replaceable>) +<replaceable>expression</replaceable> <replaceable>operator</replaceable> SOME (<replaceable>array expression</replaceable>) +</synopsis> + + <para> + The right-hand side is a parenthesized expression, which must yield an + array value. + The left-hand expression + is evaluated and compared to each element of the array using the + given <replaceable>operator</replaceable>, which must yield a Boolean + result. + The result of <token>ANY</token> is <quote>true</quote> if any true result is obtained. + The result is <quote>false</quote> if no true result is found (including the + case where the array has zero elements). + </para> + + <para> + If the array expression yields a null array, the result of + <token>ANY</token> will be null. If the left-hand expression yields null, + the result of <token>ANY</token> is ordinarily null (though a non-strict + comparison operator could possibly yield a different result). + Also, if the right-hand array contains any null elements and no true + comparison result is obtained, the result of <token>ANY</token> + will be null, not false (again, assuming a strict comparison operator). + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + </para> + + <para> + <token>SOME</token> is a synonym for <token>ANY</token>. + </para> + </sect2> + + <sect2 id="functions-comparisons-all"> + <title><literal>ALL</literal> (array)</title> + +<synopsis> +<replaceable>expression</replaceable> <replaceable>operator</replaceable> ALL (<replaceable>array expression</replaceable>) +</synopsis> + + <para> + The right-hand side is a parenthesized expression, which must yield an + array value. + The left-hand expression + is evaluated and compared to each element of the array using the + given <replaceable>operator</replaceable>, which must yield a Boolean + result. + The result of <token>ALL</token> is <quote>true</quote> if all comparisons yield true + (including the case where the array has zero elements). + The result is <quote>false</quote> if any false result is found. + </para> + + <para> + If the array expression yields a null array, the result of + <token>ALL</token> will be null. If the left-hand expression yields null, + the result of <token>ALL</token> is ordinarily null (though a non-strict + comparison operator could possibly yield a different result). + Also, if the right-hand array contains any null elements and no false + comparison result is obtained, the result of <token>ALL</token> + will be null, not true (again, assuming a strict comparison operator). + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + </para> + </sect2> + + <sect2 id="row-wise-comparison"> + <title>Row Constructor Comparison</title> + +<synopsis> +<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> <replaceable>row_constructor</replaceable> +</synopsis> + + <para> + Each side is a row constructor, + as described in <xref linkend="sql-syntax-row-constructors"/>. + The two row constructors must have the same number of fields. + The given <replaceable>operator</replaceable> is applied to each pair + of corresponding fields. (Since the fields could be of different + types, this means that a different specific operator could be selected + for each pair.) + All the selected operators must be members of some B-tree operator + class, or be the negator of an <literal>=</literal> member of a B-tree + operator class, meaning that row constructor comparison is only + possible when the <replaceable>operator</replaceable> is + <literal>=</literal>, + <literal><></literal>, + <literal><</literal>, + <literal><=</literal>, + <literal>></literal>, or + <literal>>=</literal>, + or has semantics similar to one of these. + </para> + + <para> + The <literal>=</literal> and <literal><></literal> cases work slightly differently + from the others. Two rows are considered + equal if all their corresponding members are non-null and equal; the rows + are unequal if any corresponding members are non-null and unequal; + otherwise the result of the row comparison is unknown (null). + </para> + + <para> + For the <literal><</literal>, <literal><=</literal>, <literal>></literal> and + <literal>>=</literal> cases, the row elements are compared left-to-right, + stopping as soon as an unequal or null pair of elements is found. + If either of this pair of elements is null, the result of the + row comparison is unknown (null); otherwise comparison of this pair + of elements determines the result. For example, + <literal>ROW(1,2,NULL) < ROW(1,3,0)</literal> + yields true, not null, because the third pair of elements are not + considered. + </para> + +<synopsis> +<replaceable>row_constructor</replaceable> IS DISTINCT FROM <replaceable>row_constructor</replaceable> +</synopsis> + + <para> + This construct is similar to a <literal><></literal> row comparison, + but it does not yield null for null inputs. Instead, any null value is + considered unequal to (distinct from) any non-null value, and any two + nulls are considered equal (not distinct). Thus the result will + either be true or false, never null. + </para> + +<synopsis> +<replaceable>row_constructor</replaceable> IS NOT DISTINCT FROM <replaceable>row_constructor</replaceable> +</synopsis> + + <para> + This construct is similar to a <literal>=</literal> row comparison, + but it does not yield null for null inputs. Instead, any null value is + considered unequal to (distinct from) any non-null value, and any two + nulls are considered equal (not distinct). Thus the result will always + be either true or false, never null. + </para> + + </sect2> + + <sect2 id="composite-type-comparison"> + <title>Composite Type Comparison</title> + +<synopsis> +<replaceable>record</replaceable> <replaceable>operator</replaceable> <replaceable>record</replaceable> +</synopsis> + + <para> + The SQL specification requires row-wise comparison to return NULL if the + result depends on comparing two NULL values or a NULL and a non-NULL. + <productname>PostgreSQL</productname> does this only when comparing the + results of two row constructors (as in + <xref linkend="row-wise-comparison"/>) or comparing a row constructor + to the output of a subquery (as in <xref linkend="functions-subquery"/>). + In other contexts where two composite-type values are compared, two + NULL field values are considered equal, and a NULL is considered larger + than a non-NULL. This is necessary in order to have consistent sorting + and indexing behavior for composite types. + </para> + + <para> + Each side is evaluated and they are compared row-wise. Composite type + comparisons are allowed when the <replaceable>operator</replaceable> is + <literal>=</literal>, + <literal><></literal>, + <literal><</literal>, + <literal><=</literal>, + <literal>></literal> or + <literal>>=</literal>, + or has semantics similar to one of these. (To be specific, an operator + can be a row comparison operator if it is a member of a B-tree operator + class, or is the negator of the <literal>=</literal> member of a B-tree operator + class.) The default behavior of the above operators is the same as for + <literal>IS [ NOT ] DISTINCT FROM</literal> for row constructors (see + <xref linkend="row-wise-comparison"/>). + </para> + + <para> + To support matching of rows which include elements without a default + B-tree operator class, the following operators are defined for composite + type comparison: + <literal>*=</literal>, + <literal>*<></literal>, + <literal>*<</literal>, + <literal>*<=</literal>, + <literal>*></literal>, and + <literal>*>=</literal>. + These operators compare the internal binary representation of the two + rows. Two rows might have a different binary representation even + though comparisons of the two rows with the equality operator is true. + The ordering of rows under these comparison operators is deterministic + but not otherwise meaningful. These operators are used internally + for materialized views and might be useful for other specialized + purposes such as replication and B-Tree deduplication (see <xref + linkend="btree-deduplication"/>). They are not intended to be + generally useful for writing queries, though. + </para> + </sect2> + </sect1> |