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.23. Subquery Expressions</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-window.html" title="9.22. Window Functions" /><link rel="next" href="functions-comparisons.html" title="9.24. Row and Array Comparisons" /></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.23. Subquery Expressions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-window.html" title="9.22. Window Functions">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-comparisons.html" title="9.24. Row and Array Comparisons">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-SUBQUERY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.23. Subquery Expressions <a href="#FUNCTIONS-SUBQUERY" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS">9.23.1. <code class="literal">EXISTS</code></a></span></dt><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-IN">9.23.2. <code class="literal">IN</code></a></span></dt><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-NOTIN">9.23.3. <code class="literal">NOT IN</code></a></span></dt><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME">9.23.4. <code class="literal">ANY</code>/<code class="literal">SOME</code></a></span></dt><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-ALL">9.23.5. <code class="literal">ALL</code></a></span></dt><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-SINGLE-ROW-COMP">9.23.6. Single-Row Comparison</a></span></dt></dl></div><a id="id-1.5.8.29.2" class="indexterm"></a><a id="id-1.5.8.29.3" class="indexterm"></a><a id="id-1.5.8.29.4" class="indexterm"></a><a id="id-1.5.8.29.5" class="indexterm"></a><a id="id-1.5.8.29.6" class="indexterm"></a><a id="id-1.5.8.29.7" class="indexterm"></a><a id="id-1.5.8.29.8" class="indexterm"></a><p>3 This section describes the <acronym class="acronym">SQL</acronym>-compliant subquery4 expressions available in <span class="productname">PostgreSQL</span>.5 All of the expression forms documented in this section return6 Boolean (true/false) results.7 </p><div class="sect2" id="FUNCTIONS-SUBQUERY-EXISTS"><div class="titlepage"><div><div><h3 class="title">9.23.1. <code class="literal">EXISTS</code> <a href="#FUNCTIONS-SUBQUERY-EXISTS" class="id_link">#</a></h3></div></div></div><pre class="synopsis">8EXISTS (<em class="replaceable"><code>subquery</code></em>)9</pre><p>10 The argument of <code class="token">EXISTS</code> is an arbitrary <code class="command">SELECT</code> statement,11 or <em class="firstterm">subquery</em>. The12 subquery is evaluated to determine whether it returns any rows.13 If it returns at least one row, the result of <code class="token">EXISTS</code> is14 <span class="quote">“<span class="quote">true</span>”</span>; if the subquery returns no rows, the result of <code class="token">EXISTS</code>15 is <span class="quote">“<span class="quote">false</span>”</span>.16 </p><p>17 The subquery can refer to variables from the surrounding query,18 which will act as constants during any one evaluation of the subquery.19 </p><p>20 The subquery will generally only be executed long enough to determine21 whether at least one row is returned, not all the way to completion.22 It is unwise to write a subquery that has side effects (such as23 calling sequence functions); whether the side effects occur24 might be unpredictable.25 </p><p>26 Since the result depends only on whether any rows are returned,27 and not on the contents of those rows, the output list of the28 subquery is normally unimportant. A common coding convention is29 to write all <code class="literal">EXISTS</code> tests in the form30 <code class="literal">EXISTS(SELECT 1 WHERE ...)</code>. There are exceptions to31 this rule however, such as subqueries that use <code class="token">INTERSECT</code>.32 </p><p>33 This simple example is like an inner join on <code class="literal">col2</code>, but34 it produces at most one output row for each <code class="literal">tab1</code> row,35 even if there are several matching <code class="literal">tab2</code> rows:36</p><pre class="screen">37SELECT col138FROM tab139WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);40</pre><p>41 </p></div><div class="sect2" id="FUNCTIONS-SUBQUERY-IN"><div class="titlepage"><div><div><h3 class="title">9.23.2. <code class="literal">IN</code> <a href="#FUNCTIONS-SUBQUERY-IN" class="id_link">#</a></h3></div></div></div><pre class="synopsis">42<em class="replaceable"><code>expression</code></em> IN (<em class="replaceable"><code>subquery</code></em>)43</pre><p>44 The right-hand side is a parenthesized45 subquery, which must return exactly one column. The left-hand expression46 is evaluated and compared to each row of the subquery result.47 The result of <code class="token">IN</code> is <span class="quote">“<span class="quote">true</span>”</span> if any equal subquery row is found.48 The result is <span class="quote">“<span class="quote">false</span>”</span> if no equal row is found (including the49 case where the subquery returns no rows).50 </p><p>51 Note that if the left-hand expression yields null, or if there are52 no equal right-hand values and at least one right-hand row yields53 null, the result of the <code class="token">IN</code> construct will be null, not false.54 This is in accordance with SQL's normal rules for Boolean combinations55 of null values.56 </p><p>57 As with <code class="token">EXISTS</code>, it's unwise to assume that the subquery will58 be evaluated completely.59 </p><pre class="synopsis">60<em class="replaceable"><code>row_constructor</code></em> IN (<em class="replaceable"><code>subquery</code></em>)61</pre><p>62 The left-hand side of this form of <code class="token">IN</code> is a row constructor,63 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>.64 The right-hand side is a parenthesized65 subquery, which must return exactly as many columns as there are66 expressions in the left-hand row. The left-hand expressions are67 evaluated and compared row-wise to each row of the subquery result.68 The result of <code class="token">IN</code> is <span class="quote">“<span class="quote">true</span>”</span> if any equal subquery row is found.69 The result is <span class="quote">“<span class="quote">false</span>”</span> if no equal row is found (including the70 case where the subquery returns no rows).71 </p><p>72 As usual, null values in the rows are combined per73 the normal rules of SQL Boolean expressions. Two rows are considered74 equal if all their corresponding members are non-null and equal; the rows75 are unequal if any corresponding members are non-null and unequal;76 otherwise the result of that row comparison is unknown (null).77 If all the per-row results are either unequal or null, with at least one78 null, then the result of <code class="token">IN</code> is null.79 </p></div><div class="sect2" id="FUNCTIONS-SUBQUERY-NOTIN"><div class="titlepage"><div><div><h3 class="title">9.23.3. <code class="literal">NOT IN</code> <a href="#FUNCTIONS-SUBQUERY-NOTIN" class="id_link">#</a></h3></div></div></div><pre class="synopsis">80<em class="replaceable"><code>expression</code></em> NOT IN (<em class="replaceable"><code>subquery</code></em>)81</pre><p>82 The right-hand side is a parenthesized83 subquery, which must return exactly one column. The left-hand expression84 is evaluated and compared to each row of the subquery result.85 The result of <code class="token">NOT IN</code> is <span class="quote">“<span class="quote">true</span>”</span> if only unequal subquery rows86 are found (including the case where the subquery returns no rows).87 The result is <span class="quote">“<span class="quote">false</span>”</span> if any equal row is found.88 </p><p>89 Note that if the left-hand expression yields null, or if there are90 no equal right-hand values and at least one right-hand row yields91 null, the result of the <code class="token">NOT IN</code> construct will be null, not true.92 This is in accordance with SQL's normal rules for Boolean combinations93 of null values.94 </p><p>95 As with <code class="token">EXISTS</code>, it's unwise to assume that the subquery will96 be evaluated completely.97 </p><pre class="synopsis">98<em class="replaceable"><code>row_constructor</code></em> NOT IN (<em class="replaceable"><code>subquery</code></em>)99</pre><p>100 The left-hand side of this form of <code class="token">NOT IN</code> is a row constructor,101 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>.102 The right-hand side is a parenthesized103 subquery, which must return exactly as many columns as there are104 expressions in the left-hand row. The left-hand expressions are105 evaluated and compared row-wise to each row of the subquery result.106 The result of <code class="token">NOT IN</code> is <span class="quote">“<span class="quote">true</span>”</span> if only unequal subquery rows107 are found (including the case where the subquery returns no rows).108 The result is <span class="quote">“<span class="quote">false</span>”</span> if any equal row is found.109 </p><p>110 As usual, null values in the rows are combined per111 the normal rules of SQL Boolean expressions. Two rows are considered112 equal if all their corresponding members are non-null and equal; the rows113 are unequal if any corresponding members are non-null and unequal;114 otherwise the result of that row comparison is unknown (null).115 If all the per-row results are either unequal or null, with at least one116 null, then the result of <code class="token">NOT IN</code> is null.117 </p></div><div class="sect2" id="FUNCTIONS-SUBQUERY-ANY-SOME"><div class="titlepage"><div><div><h3 class="title">9.23.4. <code class="literal">ANY</code>/<code class="literal">SOME</code> <a href="#FUNCTIONS-SUBQUERY-ANY-SOME" class="id_link">#</a></h3></div></div></div><pre class="synopsis">118<em class="replaceable"><code>expression</code></em> <em class="replaceable"><code>operator</code></em> ANY (<em class="replaceable"><code>subquery</code></em>)119<em class="replaceable"><code>expression</code></em> <em class="replaceable"><code>operator</code></em> SOME (<em class="replaceable"><code>subquery</code></em>)120</pre><p>121 The right-hand side is a parenthesized122 subquery, which must return exactly one column. The left-hand expression123 is evaluated and compared to each row of the subquery result using the124 given <em class="replaceable"><code>operator</code></em>, which must yield a Boolean125 result.126 The result of <code class="token">ANY</code> is <span class="quote">“<span class="quote">true</span>”</span> if any true result is obtained.127 The result is <span class="quote">“<span class="quote">false</span>”</span> if no true result is found (including the128 case where the subquery returns no rows).129 </p><p>130 <code class="token">SOME</code> is a synonym for <code class="token">ANY</code>.131 <code class="token">IN</code> is equivalent to <code class="literal">= ANY</code>.132 </p><p>133 Note that if there are no successes and at least one right-hand row yields134 null for the operator's result, the result of the <code class="token">ANY</code> construct135 will be null, not false.136 This is in accordance with SQL's normal rules for Boolean combinations137 of null values.138 </p><p>139 As with <code class="token">EXISTS</code>, it's unwise to assume that the subquery will140 be evaluated completely.141 </p><pre class="synopsis">142<em class="replaceable"><code>row_constructor</code></em> <em class="replaceable"><code>operator</code></em> ANY (<em class="replaceable"><code>subquery</code></em>)143<em class="replaceable"><code>row_constructor</code></em> <em class="replaceable"><code>operator</code></em> SOME (<em class="replaceable"><code>subquery</code></em>)144</pre><p>145 The left-hand side of this form of <code class="token">ANY</code> is a row constructor,146 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>.147 The right-hand side is a parenthesized148 subquery, which must return exactly as many columns as there are149 expressions in the left-hand row. The left-hand expressions are150 evaluated and compared row-wise to each row of the subquery result,151 using the given <em class="replaceable"><code>operator</code></em>.152 The result of <code class="token">ANY</code> is <span class="quote">“<span class="quote">true</span>”</span> if the comparison153 returns true for any subquery row.154 The result is <span class="quote">“<span class="quote">false</span>”</span> if the comparison returns false for every155 subquery row (including the case where the subquery returns no156 rows).157 The result is NULL if no comparison with a subquery row returns true,158 and at least one comparison returns NULL.159 </p><p>160 See <a class="xref" href="functions-comparisons.html#ROW-WISE-COMPARISON" title="9.24.5. Row Constructor Comparison">Section 9.24.5</a> for details about the meaning161 of a row constructor comparison.162 </p></div><div class="sect2" id="FUNCTIONS-SUBQUERY-ALL"><div class="titlepage"><div><div><h3 class="title">9.23.5. <code class="literal">ALL</code> <a href="#FUNCTIONS-SUBQUERY-ALL" class="id_link">#</a></h3></div></div></div><pre class="synopsis">163<em class="replaceable"><code>expression</code></em> <em class="replaceable"><code>operator</code></em> ALL (<em class="replaceable"><code>subquery</code></em>)164</pre><p>165 The right-hand side is a parenthesized166 subquery, which must return exactly one column. The left-hand expression167 is evaluated and compared to each row of the subquery result using the168 given <em class="replaceable"><code>operator</code></em>, which must yield a Boolean169 result.170 The result of <code class="token">ALL</code> is <span class="quote">“<span class="quote">true</span>”</span> if all rows yield true171 (including the case where the subquery returns no rows).172 The result is <span class="quote">“<span class="quote">false</span>”</span> if any false result is found.173 The result is NULL if no comparison with a subquery row returns false,174 and at least one comparison returns NULL.175 </p><p>176 <code class="token">NOT IN</code> is equivalent to <code class="literal"><> ALL</code>.177 </p><p>178 As with <code class="token">EXISTS</code>, it's unwise to assume that the subquery will179 be evaluated completely.180 </p><pre class="synopsis">181<em class="replaceable"><code>row_constructor</code></em> <em class="replaceable"><code>operator</code></em> ALL (<em class="replaceable"><code>subquery</code></em>)182</pre><p>183 The left-hand side of this form of <code class="token">ALL</code> is a row constructor,184 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>.185 The right-hand side is a parenthesized186 subquery, which must return exactly as many columns as there are187 expressions in the left-hand row. The left-hand expressions are188 evaluated and compared row-wise to each row of the subquery result,189 using the given <em class="replaceable"><code>operator</code></em>.190 The result of <code class="token">ALL</code> is <span class="quote">“<span class="quote">true</span>”</span> if the comparison191 returns true for all subquery rows (including the192 case where the subquery returns no rows).193 The result is <span class="quote">“<span class="quote">false</span>”</span> if the comparison returns false for any194 subquery row.195 The result is NULL if no comparison with a subquery row returns false,196 and at least one comparison returns NULL.197 </p><p>198 See <a class="xref" href="functions-comparisons.html#ROW-WISE-COMPARISON" title="9.24.5. Row Constructor Comparison">Section 9.24.5</a> for details about the meaning199 of a row constructor comparison.200 </p></div><div class="sect2" id="FUNCTIONS-SUBQUERY-SINGLE-ROW-COMP"><div class="titlepage"><div><div><h3 class="title">9.23.6. Single-Row Comparison <a href="#FUNCTIONS-SUBQUERY-SINGLE-ROW-COMP" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.29.15.2" class="indexterm"></a><pre class="synopsis">201<em class="replaceable"><code>row_constructor</code></em> <em class="replaceable"><code>operator</code></em> (<em class="replaceable"><code>subquery</code></em>)202</pre><p>203 The left-hand side is a row constructor,204 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>.205 The right-hand side is a parenthesized subquery, which must return exactly206 as many columns as there are expressions in the left-hand row. Furthermore,207 the subquery cannot return more than one row. (If it returns zero rows,208 the result is taken to be null.) The left-hand side is evaluated and209 compared row-wise to the single subquery result row.210 </p><p>211 See <a class="xref" href="functions-comparisons.html#ROW-WISE-COMPARISON" title="9.24.5. Row Constructor Comparison">Section 9.24.5</a> for details about the meaning212 of a row constructor comparison.213 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-window.html" title="9.22. Window Functions">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-comparisons.html" title="9.24. Row and Array Comparisons">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.22. Window Functions </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.24. Row and Array Comparisons</td></tr></table></div></body></html>