From bee217924d51af8cf0411167bfc8a7eb55122577 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 29 Jun 2003 00:33:44 +0000 Subject: Support expressions of the form 'scalar op ANY (array)' and 'scalar op ALL (array)', where the operator is applied between the lefthand scalar and each element of the array. The operator must yield boolean; the result of the construct is the OR or AND of the per-element results, respectively. Original coding by Joe Conway, after an idea of Peter's. Rewritten by Tom to keep the implementation strictly separate from subqueries. --- doc/src/sgml/func.sgml | 290 +++++++++++++++++++++++++++++++++---------------- 1 file changed, 194 insertions(+), 96 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index df677df37a2..e15a44570b1 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -7457,52 +7457,14 @@ SELECT col1 FROM tab1 - <literal>IN</literal> (scalar form) - - -expression IN (value, ...) - - - - The right-hand side of this form of IN is a parenthesized list - of scalar expressions. The result is true if the left-hand expression's - result is equal to any of the right-hand expressions. This is a shorthand - notation for - - -expression = value1 -OR -expression = value2 -OR -... - - - - - 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 IN construct will be null, not false. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. - - - - - This form of IN is not truly a subquery expression, but it - seems best to document it in the same place as subquery IN. - - - - - - <literal>IN</literal> (subquery form) + <literal>IN</literal> expression IN (subquery) - The right-hand side of this form of IN is a parenthesized + The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result of IN is true if any equal subquery row is found. @@ -7538,7 +7500,7 @@ OR - As usual, null values in the expressions or subquery rows are combined per + As usual, null values in the rows are combined per the normal rules of SQL Boolean expressions. 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; @@ -7549,55 +7511,14 @@ OR - <literal>NOT IN</literal> (scalar form) - - -expression NOT IN (value, ...) - - - - The right-hand side of this form of NOT IN is a parenthesized list - of scalar expressions. The result is true if the left-hand expression's - result is unequal to all of the right-hand expressions. This is a shorthand - notation for - - -expression <> value1 -AND -expression <> value2 -AND -... - - - - - 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 NOT IN 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. - - - - - x NOT IN y is equivalent to NOT (x IN y) in all - cases. However, null values are much more likely to trip up the novice when - working with NOT IN than when working with IN. - It's best to express your condition positively if possible. - - - - - - <literal>NOT IN </literal>(subquery form) + <literal>NOT IN </literal> expression NOT IN (subquery) - The right-hand side of this form of NOT IN is a parenthesized + The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result of NOT IN is true if only unequal subquery rows @@ -7633,7 +7554,7 @@ AND - As usual, null values in the expressions or subquery rows are combined per + As usual, null values in the rows are combined per the normal rules of SQL Boolean expressions. 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; @@ -7652,7 +7573,7 @@ AND - The right-hand side of this form of ANY is a parenthesized + The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean @@ -7700,7 +7621,7 @@ AND - As usual, null values in the expressions or subquery rows are combined per + As usual, null values in the rows are combined per the normal rules of SQL Boolean expressions. 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; @@ -7718,7 +7639,7 @@ AND - The right-hand side of this form of ALL is a parenthesized + The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean @@ -7765,7 +7686,7 @@ AND - As usual, null values in the expressions or subquery rows are combined per + As usual, null values in the rows are combined per the normal rules of SQL Boolean expressions. 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; @@ -7780,24 +7701,201 @@ AND (expression , expression ...) operator (subquery) -(expression , expression ...) operator (expression , expression ...) - The left-hand side is a list of scalar expressions. The right-hand side - can be either a list of scalar expressions of the same length, or a - parenthesized subquery, which must return exactly as many columns as there + The left-hand side is a list of scalar expressions. The right-hand side is + a parenthesized subquery, which must return exactly as many columns as there are expressions on the left-hand side. Furthermore, the subquery cannot return more than one row. (If it returns zero rows, the result is taken to be null.) The left-hand side is evaluated and compared row-wise to the - single subquery result row, or to the right-hand expression list. + single subquery result row. + Presently, only = and <> operators are allowed + in row-wise comparisons. + The result is true if the two rows are equal or unequal, respectively. + + + + As usual, null values in the rows are combined per + the normal rules of SQL Boolean expressions. 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). + + + + + + + Row and Array Comparisons + + + in + + + + not in + + + + any + + + + all + + + + some + + + + 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 + PostgreSQL extensions; the rest are + SQL-compliant. + All of the expression forms documented in this section return + Boolean (true/false) results. + + + + <literal>IN</literal> + + +expression IN (value, ...) + + + + The right-hand side is a parenthesized list + of scalar expressions. The result is true if the left-hand expression's + result is equal to any of the right-hand expressions. This is a shorthand + notation for + + +expression = value1 +OR +expression = value2 +OR +... + + + + + 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 IN construct will be null, not false. + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + + + + + <literal>NOT IN</literal> + + +expression NOT IN (value, ...) + + + + The right-hand side is a parenthesized list + of scalar expressions. The result is true if the left-hand expression's + result is unequal to all of the right-hand expressions. This is a shorthand + notation for + + +expression <> value1 +AND +expression <> value2 +AND +... + + + + + 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 NOT IN 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. + + + + + x NOT IN y is equivalent to NOT (x IN y) in all + cases. However, null values are much more likely to trip up the novice when + working with NOT IN than when working with IN. + It's best to express your condition positively if possible. + + + + + + <literal>ANY</literal>/<literal>SOME</literal> (array) + + +expression operator ANY (array expression) +expression operator SOME (array expression) + + + + 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 operator, which must yield a Boolean + result. + The result of ANY is true if any true result is obtained. + The result is false if no true result is found (including the special + case where the array has zero elements). + + + + SOME is a synonym for ANY. + + + + + <literal>ALL</literal> (array) + + +expression operator ALL (array expression) + + + + 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 operator, which must yield a Boolean + result. + The result of ALL is true if all comparisons yield true + (including the special case where the array has zero elements). + The result is false if any false result is found. + + + + + Row-wise Comparison + + +(expression , expression ...) operator (expression , expression ...) + + + + Each side is a list of scalar expressions; the two lists must be + of the same length. Each side is evaluated and they are compared + row-wise. Presently, only = and <> operators are allowed in row-wise comparisons. The result is true if the two rows are equal or unequal, respectively. - As usual, null values in the expressions or subquery rows are combined per + As usual, null values in the rows are combined per the normal rules of SQL Boolean expressions. 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; -- cgit v1.2.3