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.25. 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.24. Subquery Expressions" /><link rel="next" href="functions-srf.html" title="9.26. 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.25. Row and Array Comparisons</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-subquery.html" title="9.24. 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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-srf.html" title="9.26. 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.25. 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.25.1. <code class="literal">IN</code></a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#FUNCTIONS-COMPARISONS-NOT-IN">9.25.2. <code class="literal">NOT IN</code></a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#FUNCTIONS-COMPARISONS-ANY-SOME">9.25.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.25.4. <code class="literal">ALL</code> (array)</a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#ROW-WISE-COMPARISON">9.25.5. Row Constructor Comparison</a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#COMPOSITE-TYPE-COMPARISON">9.25.6. Composite Type Comparison</a></span></dt></dl></div><a id="id-1.5.8.31.2" class="indexterm"></a><a id="id-1.5.8.31.3" class="indexterm"></a><a id="id-1.5.8.31.4" class="indexterm"></a><a id="id-1.5.8.31.5" class="indexterm"></a><a id="id-1.5.8.31.6" class="indexterm"></a><a id="id-1.5.8.31.7" class="indexterm"></a><a id="id-1.5.8.31.8" class="indexterm"></a><a id="id-1.5.8.31.9" class="indexterm"></a><a id="id-1.5.8.31.10" class="indexterm"></a><a id="id-1.5.8.31.11" class="indexterm"></a><a id="id-1.5.8.31.12" class="indexterm"></a><p>
3 This section describes several specialized constructs for making
4 multiple comparisons between groups of values. These forms are
5 syntactically related to the subquery forms of the previous section,
6 but do not involve subqueries.
7 The forms involving array subexpressions are
8 <span class="productname">PostgreSQL</span> extensions; the rest are
9 <acronym class="acronym">SQL</acronym>-compliant.
10 All of the expression forms documented in this section return
11 Boolean (true/false) results.
12 </p><div class="sect2" id="FUNCTIONS-COMPARISONS-IN-SCALAR"><div class="titlepage"><div><div><h3 class="title">9.25.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>])
15 The right-hand side is a parenthesized list
16 of expressions. The result is <span class="quote">“<span class="quote">true</span>”</span> if the left-hand expression's
17 result is equal to any of the right-hand expressions. This is a shorthand
20 </p><pre class="synopsis">
21 <em class="replaceable"><code>expression</code></em> = <em class="replaceable"><code>value1</code></em>
23 <em class="replaceable"><code>expression</code></em> = <em class="replaceable"><code>value2</code></em>
28 Note that if the left-hand expression yields null, or if there are
29 no equal right-hand values and at least one right-hand expression yields
30 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 combinations
33 </p></div><div class="sect2" id="FUNCTIONS-COMPARISONS-NOT-IN"><div class="titlepage"><div><div><h3 class="title">9.25.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>])
36 The right-hand side is a parenthesized list
37 of expressions. The result is <span class="quote">“<span class="quote">true</span>”</span> if the left-hand expression's
38 result is unequal to all of the right-hand expressions. This is a shorthand
41 </p><pre class="synopsis">
42 <em class="replaceable"><code>expression</code></em> <> <em class="replaceable"><code>value1</code></em>
44 <em class="replaceable"><code>expression</code></em> <> <em class="replaceable"><code>value2</code></em>
49 Note that if the left-hand expression yields null, or if there are
50 no equal right-hand values and at least one right-hand expression yields
51 null, the result of the <code class="token">NOT IN</code> construct will be null, not true
52 as one might naively expect.
53 This is in accordance with SQL's normal rules for Boolean combinations
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 all
57 cases. However, null values are much more likely to trip up the novice when
58 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.25.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>)
64 The right-hand side is a parenthesized expression, which must yield an
66 The left-hand expression
67 is evaluated and compared to each element of the array using the
68 given <em class="replaceable"><code>operator</code></em>, which must yield a Boolean
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 the
72 case where the array has zero elements).
74 If the array expression yields a null array, the result of
75 <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-strict
77 comparison operator could possibly yield a different result).
78 Also, if the right-hand array contains any null elements and no true
79 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 combinations
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.25.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>)
88 The right-hand side is a parenthesized expression, which must yield an
90 The left-hand expression
91 is evaluated and compared to each element of the array using the
92 given <em class="replaceable"><code>operator</code></em>, which must yield a Boolean
94 The result of <code class="token">ALL</code> is <span class="quote">“<span class="quote">true</span>”</span> if all comparisons yield true
95 (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.
98 If the array expression yields a null array, the result of
99 <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-strict
101 comparison operator could possibly yield a different result).
102 Also, if the right-hand array contains any null elements and no false
103 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 combinations
107 </p></div><div class="sect2" id="ROW-WISE-COMPARISON"><div class="titlepage"><div><div><h3 class="title">9.25.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>
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 pair
114 of corresponding fields. (Since the fields could be of different
115 types, this means that a different specific operator could be selected
117 All the selected operators must be members of some B-tree operator
118 class, or be the negator of an <code class="literal">=</code> member of a B-tree
119 operator class, meaning that row constructor comparison is only
120 possible when the <em class="replaceable"><code>operator</code></em> is
121 <code class="literal">=</code>,
122 <code class="literal"><></code>,
123 <code class="literal"><</code>,
124 <code class="literal"><=</code>,
125 <code class="literal">></code>, or
126 <code class="literal">>=</code>,
127 or has semantics similar to one of these.
129 The <code class="literal">=</code> and <code class="literal"><></code> cases work slightly differently
130 from the others. Two rows are considered
131 equal if all their corresponding members are non-null and equal; the rows
132 are unequal if any corresponding members are non-null and unequal;
133 otherwise the result of the row comparison is unknown (null).
135 For the <code class="literal"><</code>, <code class="literal"><=</code>, <code class="literal">></code> and
136 <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 the
139 row comparison is unknown (null); otherwise comparison of this pair
140 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 not
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>
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 is
149 considered unequal to (distinct from) any non-null value, and any two
150 nulls are considered equal (not distinct). Thus the result will
151 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>
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 is
157 considered unequal to (distinct from) any non-null value, and any two
158 nulls are considered equal (not distinct). Thus the result will always
159 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.25.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>
163 The SQL specification requires row-wise comparison to return NULL if the
164 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 the
166 results of two row constructors (as in
167 <a class="xref" href="functions-comparisons.html#ROW-WISE-COMPARISON" title="9.25.5. Row Constructor Comparison">Section 9.25.5</a>) or comparing a row constructor
168 to the output of a subquery (as in <a class="xref" href="functions-subquery.html" title="9.24. Subquery Expressions">Section 9.24</a>).
169 In other contexts where two composite-type values are compared, two
170 NULL field values are considered equal, and a NULL is considered larger
171 than a non-NULL. This is necessary in order to have consistent sorting
172 and indexing behavior for composite types.
174 Each side is evaluated and they are compared row-wise. Composite type
175 comparisons are allowed when the <em class="replaceable"><code>operator</code></em> is
176 <code class="literal">=</code>,
177 <code class="literal"><></code>,
178 <code class="literal"><</code>,
179 <code class="literal"><=</code>,
180 <code class="literal">></code> or
181 <code class="literal">>=</code>,
182 or has semantics similar to one of these. (To be specific, an operator
183 can be a row comparison operator if it is a member of a B-tree operator
184 class, or is the negator of the <code class="literal">=</code> member of a B-tree operator
185 class.) The default behavior of the above operators is the same as for
186 <code class="literal">IS [ NOT ] DISTINCT FROM</code> for row constructors (see
187 <a class="xref" href="functions-comparisons.html#ROW-WISE-COMPARISON" title="9.25.5. Row Constructor Comparison">Section 9.25.5</a>).
189 To support matching of rows which include elements without a default
190 B-tree operator class, the following operators are defined for composite
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>, and
197 <code class="literal">*>=</code>.
198 These operators compare the internal binary representation of the two
199 rows. Two rows might have a different binary representation even
200 though comparisons of the two rows with the equality operator is true.
201 The ordering of rows under these comparison operators is deterministic
202 but not otherwise meaningful. These operators are used internally
203 for materialized views and might be useful for other specialized
204 purposes such as replication and B-Tree deduplication (see <a class="xref" href="btree.html#BTREE-DEDUPLICATION" title="65.1.4.3. Deduplication">Section 65.1.4.3</a>). They are not intended to be
205 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.24. 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.26. Set Returning Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.24. Subquery Expressions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.26. Set Returning Functions</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob