codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.24. Row and Array Comparisons</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-subquery.html" title="9.23. Subquery Expressions" /><link rel="next" href="functions-srf.html" title="9.25. Set Returning Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.24. Row and Array Comparisons</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-subquery.html" title="9.23. Subquery Expressions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-srf.html" title="9.25. Set Returning Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-COMPARISONS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.24. Row and Array Comparisons <a href="#FUNCTIONS-COMPARISONS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR">9.24.1. <code class="literal">IN</code></a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#FUNCTIONS-COMPARISONS-NOT-IN">9.24.2. <code class="literal">NOT IN</code></a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#FUNCTIONS-COMPARISONS-ANY-SOME">9.24.3. <code class="literal">ANY</code>/<code class="literal">SOME</code> (array)</a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#FUNCTIONS-COMPARISONS-ALL">9.24.4. <code class="literal">ALL</code> (array)</a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#ROW-WISE-COMPARISON">9.24.5. Row Constructor Comparison</a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#COMPOSITE-TYPE-COMPARISON">9.24.6. Composite Type Comparison</a></span></dt></dl></div><a id="id-1.5.8.30.2" class="indexterm"></a><a id="id-1.5.8.30.3" class="indexterm"></a><a id="id-1.5.8.30.4" class="indexterm"></a><a id="id-1.5.8.30.5" class="indexterm"></a><a id="id-1.5.8.30.6" class="indexterm"></a><a id="id-1.5.8.30.7" class="indexterm"></a><a id="id-1.5.8.30.8" class="indexterm"></a><a id="id-1.5.8.30.9" class="indexterm"></a><a id="id-1.5.8.30.10" class="indexterm"></a><a id="id-1.5.8.30.11" class="indexterm"></a><a id="id-1.5.8.30.12" class="indexterm"></a><p>3 This section describes several specialized constructs for making4 multiple comparisons between groups of values. These forms are5 syntactically related to the subquery forms of the previous section,6 but do not involve subqueries.7 The forms involving array subexpressions are8 <span class="productname">PostgreSQL</span> extensions; the rest are9 <acronym class="acronym">SQL</acronym>-compliant.10 All of the expression forms documented in this section return11 Boolean (true/false) results.12 </p><div class="sect2" id="FUNCTIONS-COMPARISONS-IN-SCALAR"><div class="titlepage"><div><div><h3 class="title">9.24.1. <code class="literal">IN</code> <a href="#FUNCTIONS-COMPARISONS-IN-SCALAR" class="id_link">#</a></h3></div></div></div><pre class="synopsis">13<em class="replaceable"><code>expression</code></em> IN (<em class="replaceable"><code>value</code></em> [<span class="optional">, ...</span>])14</pre><p>15 The right-hand side is a parenthesized list16 of expressions. The result is <span class="quote">“<span class="quote">true</span>”</span> if the left-hand expression's17 result is equal to any of the right-hand expressions. This is a shorthand18 notation for19 20</p><pre class="synopsis">21<em class="replaceable"><code>expression</code></em> = <em class="replaceable"><code>value1</code></em>22OR23<em class="replaceable"><code>expression</code></em> = <em class="replaceable"><code>value2</code></em>24OR25...26</pre><p>27 </p><p>28 Note that if the left-hand expression yields null, or if there are29 no equal right-hand values and at least one right-hand expression yields30 null, the result of the <code class="token">IN</code> construct will be null, not false.31 This is in accordance with SQL's normal rules for Boolean combinations32 of null values.33 </p></div><div class="sect2" id="FUNCTIONS-COMPARISONS-NOT-IN"><div class="titlepage"><div><div><h3 class="title">9.24.2. <code class="literal">NOT IN</code> <a href="#FUNCTIONS-COMPARISONS-NOT-IN" class="id_link">#</a></h3></div></div></div><pre class="synopsis">34<em class="replaceable"><code>expression</code></em> NOT IN (<em class="replaceable"><code>value</code></em> [<span class="optional">, ...</span>])35</pre><p>36 The right-hand side is a parenthesized list37 of expressions. The result is <span class="quote">“<span class="quote">true</span>”</span> if the left-hand expression's38 result is unequal to all of the right-hand expressions. This is a shorthand39 notation for40 41</p><pre class="synopsis">42<em class="replaceable"><code>expression</code></em> <> <em class="replaceable"><code>value1</code></em>43AND44<em class="replaceable"><code>expression</code></em> <> <em class="replaceable"><code>value2</code></em>45AND46...47</pre><p>48 </p><p>49 Note that if the left-hand expression yields null, or if there are50 no equal right-hand values and at least one right-hand expression yields51 null, the result of the <code class="token">NOT IN</code> construct will be null, not true52 as one might naively expect.53 This is in accordance with SQL's normal rules for Boolean combinations54 of null values.55 </p><div class="tip"><h3 class="title">Tip</h3><p>56 <code class="literal">x NOT IN y</code> is equivalent to <code class="literal">NOT (x IN y)</code> in all57 cases. However, null values are much more likely to trip up the novice when58 working with <code class="token">NOT IN</code> than when working with <code class="token">IN</code>.59 It is best to express your condition positively if possible.60 </p></div></div><div class="sect2" id="FUNCTIONS-COMPARISONS-ANY-SOME"><div class="titlepage"><div><div><h3 class="title">9.24.3. <code class="literal">ANY</code>/<code class="literal">SOME</code> (array) <a href="#FUNCTIONS-COMPARISONS-ANY-SOME" class="id_link">#</a></h3></div></div></div><pre class="synopsis">61<em class="replaceable"><code>expression</code></em> <em class="replaceable"><code>operator</code></em> ANY (<em class="replaceable"><code>array expression</code></em>)62<em class="replaceable"><code>expression</code></em> <em class="replaceable"><code>operator</code></em> SOME (<em class="replaceable"><code>array expression</code></em>)63</pre><p>64 The right-hand side is a parenthesized expression, which must yield an65 array value.66 The left-hand expression67 is evaluated and compared to each element of the array using the68 given <em class="replaceable"><code>operator</code></em>, which must yield a Boolean69 result.70 The result of <code class="token">ANY</code> is <span class="quote">“<span class="quote">true</span>”</span> if any true result is obtained.71 The result is <span class="quote">“<span class="quote">false</span>”</span> if no true result is found (including the72 case where the array has zero elements).73 </p><p>74 If the array expression yields a null array, the result of75 <code class="token">ANY</code> will be null. If the left-hand expression yields null,76 the result of <code class="token">ANY</code> is ordinarily null (though a non-strict77 comparison operator could possibly yield a different result).78 Also, if the right-hand array contains any null elements and no true79 comparison result is obtained, the result of <code class="token">ANY</code>80 will be null, not false (again, assuming a strict comparison operator).81 This is in accordance with SQL's normal rules for Boolean combinations82 of null values.83 </p><p>84 <code class="token">SOME</code> is a synonym for <code class="token">ANY</code>.85 </p></div><div class="sect2" id="FUNCTIONS-COMPARISONS-ALL"><div class="titlepage"><div><div><h3 class="title">9.24.4. <code class="literal">ALL</code> (array) <a href="#FUNCTIONS-COMPARISONS-ALL" class="id_link">#</a></h3></div></div></div><pre class="synopsis">86<em class="replaceable"><code>expression</code></em> <em class="replaceable"><code>operator</code></em> ALL (<em class="replaceable"><code>array expression</code></em>)87</pre><p>88 The right-hand side is a parenthesized expression, which must yield an89 array value.90 The left-hand expression91 is evaluated and compared to each element of the array using the92 given <em class="replaceable"><code>operator</code></em>, which must yield a Boolean93 result.94 The result of <code class="token">ALL</code> is <span class="quote">“<span class="quote">true</span>”</span> if all comparisons yield true95 (including the case where the array has zero elements).96 The result is <span class="quote">“<span class="quote">false</span>”</span> if any false result is found.97 </p><p>98 If the array expression yields a null array, the result of99 <code class="token">ALL</code> will be null. If the left-hand expression yields null,100 the result of <code class="token">ALL</code> is ordinarily null (though a non-strict101 comparison operator could possibly yield a different result).102 Also, if the right-hand array contains any null elements and no false103 comparison result is obtained, the result of <code class="token">ALL</code>104 will be null, not true (again, assuming a strict comparison operator).105 This is in accordance with SQL's normal rules for Boolean combinations106 of null values.107 </p></div><div class="sect2" id="ROW-WISE-COMPARISON"><div class="titlepage"><div><div><h3 class="title">9.24.5. Row Constructor Comparison <a href="#ROW-WISE-COMPARISON" class="id_link">#</a></h3></div></div></div><pre class="synopsis">108<em class="replaceable"><code>row_constructor</code></em> <em class="replaceable"><code>operator</code></em> <em class="replaceable"><code>row_constructor</code></em>109</pre><p>110 Each side is a row constructor,111 as described in <a class="xref" href="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" title="4.2.13. Row Constructors">Section 4.2.13</a>.112 The two row constructors must have the same number of fields.113 The given <em class="replaceable"><code>operator</code></em> is applied to each pair114 of corresponding fields. (Since the fields could be of different115 types, this means that a different specific operator could be selected116 for each pair.)117 All the selected operators must be members of some B-tree operator118 class, or be the negator of an <code class="literal">=</code> member of a B-tree119 operator class, meaning that row constructor comparison is only120 possible when the <em class="replaceable"><code>operator</code></em> is121 <code class="literal">=</code>,122 <code class="literal"><></code>,123 <code class="literal"><</code>,124 <code class="literal"><=</code>,125 <code class="literal">></code>, or126 <code class="literal">>=</code>,127 or has semantics similar to one of these.128 </p><p>129 The <code class="literal">=</code> and <code class="literal"><></code> cases work slightly differently130 from the others. Two rows are considered131 equal if all their corresponding members are non-null and equal; the rows132 are unequal if any corresponding members are non-null and unequal;133 otherwise the result of the row comparison is unknown (null).134 </p><p>135 For the <code class="literal"><</code>, <code class="literal"><=</code>, <code class="literal">></code> and136 <code class="literal">>=</code> cases, the row elements are compared left-to-right,137 stopping as soon as an unequal or null pair of elements is found.138 If either of this pair of elements is null, the result of the139 row comparison is unknown (null); otherwise comparison of this pair140 of elements determines the result. For example,141 <code class="literal">ROW(1,2,NULL) < ROW(1,3,0)</code>142 yields true, not null, because the third pair of elements are not143 considered.144 </p><pre class="synopsis">145<em class="replaceable"><code>row_constructor</code></em> IS DISTINCT FROM <em class="replaceable"><code>row_constructor</code></em>146</pre><p>147 This construct is similar to a <code class="literal"><></code> row comparison,148 but it does not yield null for null inputs. Instead, any null value is149 considered unequal to (distinct from) any non-null value, and any two150 nulls are considered equal (not distinct). Thus the result will151 either be true or false, never null.152 </p><pre class="synopsis">153<em class="replaceable"><code>row_constructor</code></em> IS NOT DISTINCT FROM <em class="replaceable"><code>row_constructor</code></em>154</pre><p>155 This construct is similar to a <code class="literal">=</code> row comparison,156 but it does not yield null for null inputs. Instead, any null value is157 considered unequal to (distinct from) any non-null value, and any two158 nulls are considered equal (not distinct). Thus the result will always159 be either true or false, never null.160 </p></div><div class="sect2" id="COMPOSITE-TYPE-COMPARISON"><div class="titlepage"><div><div><h3 class="title">9.24.6. Composite Type Comparison <a href="#COMPOSITE-TYPE-COMPARISON" class="id_link">#</a></h3></div></div></div><pre class="synopsis">161<em class="replaceable"><code>record</code></em> <em class="replaceable"><code>operator</code></em> <em class="replaceable"><code>record</code></em>162</pre><p>163 The SQL specification requires row-wise comparison to return NULL if the164 result depends on comparing two NULL values or a NULL and a non-NULL.165 <span class="productname">PostgreSQL</span> does this only when comparing the166 results of two row constructors (as in167 <a class="xref" href="functions-comparisons.html#ROW-WISE-COMPARISON" title="9.24.5. Row Constructor Comparison">Section 9.24.5</a>) or comparing a row constructor168 to the output of a subquery (as in <a class="xref" href="functions-subquery.html" title="9.23. Subquery Expressions">Section 9.23</a>).169 In other contexts where two composite-type values are compared, two170 NULL field values are considered equal, and a NULL is considered larger171 than a non-NULL. This is necessary in order to have consistent sorting172 and indexing behavior for composite types.173 </p><p>174 Each side is evaluated and they are compared row-wise. Composite type175 comparisons are allowed when the <em class="replaceable"><code>operator</code></em> is176 <code class="literal">=</code>,177 <code class="literal"><></code>,178 <code class="literal"><</code>,179 <code class="literal"><=</code>,180 <code class="literal">></code> or181 <code class="literal">>=</code>,182 or has semantics similar to one of these. (To be specific, an operator183 can be a row comparison operator if it is a member of a B-tree operator184 class, or is the negator of the <code class="literal">=</code> member of a B-tree operator185 class.) The default behavior of the above operators is the same as for186 <code class="literal">IS [ NOT ] DISTINCT FROM</code> for row constructors (see187 <a class="xref" href="functions-comparisons.html#ROW-WISE-COMPARISON" title="9.24.5. Row Constructor Comparison">Section 9.24.5</a>).188 </p><p>189 To support matching of rows which include elements without a default190 B-tree operator class, the following operators are defined for composite191 type comparison:192 <code class="literal">*=</code>,193 <code class="literal">*<></code>,194 <code class="literal">*<</code>,195 <code class="literal">*<=</code>,196 <code class="literal">*></code>, and197 <code class="literal">*>=</code>.198 These operators compare the internal binary representation of the two199 rows. Two rows might have a different binary representation even200 though comparisons of the two rows with the equality operator is true.201 The ordering of rows under these comparison operators is deterministic202 but not otherwise meaningful. These operators are used internally203 for materialized views and might be useful for other specialized204 purposes such as replication and B-Tree deduplication (see <a class="xref" href="btree-implementation.html#BTREE-DEDUPLICATION" title="67.4.3. Deduplication">Section 67.4.3</a>). They are not intended to be205 generally useful for writing queries, though.206 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-subquery.html" title="9.23. Subquery Expressions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-srf.html" title="9.25. Set Returning Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.23. Subquery Expressions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.25. Set Returning Functions</td></tr></table></div></body></html>