<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Subquery Expressions</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 9.6.21 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Functions and Operators" HREF="functions.html"><LINK REL="PREVIOUS" TITLE="Window Functions" HREF="functions-window.html"><LINK REL="NEXT" TITLE="Row and Array Comparisons" HREF="functions-comparisons.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2021-02-27T18:26:08"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="4" ALIGN="center" VALIGN="bottom" ><A HREF="index.html" >PostgreSQL 9.6.21 Documentation</A ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="Window Functions" HREF="functions-window.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 9. Functions and Operators</TD ><TD WIDTH="20%" ALIGN="right" VALIGN="top" ><A TITLE="Row and Array Comparisons" HREF="functions-comparisons.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="FUNCTIONS-SUBQUERY" >9.22. Subquery Expressions</A ></H1 ><P > This section describes the <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM >-compliant subquery expressions available in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN >. All of the expression forms documented in this section return Boolean (true/false) results. </P ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="FUNCTIONS-SUBQUERY-EXISTS" >9.22.1. <TT CLASS="LITERAL" >EXISTS</TT ></A ></H2 ><PRE CLASS="SYNOPSIS" >EXISTS (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > The argument of <TT CLASS="TOKEN" >EXISTS</TT > is an arbitrary <TT CLASS="COMMAND" >SELECT</TT > statement, or <I CLASS="FIRSTTERM" >subquery</I >. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of <TT CLASS="TOKEN" >EXISTS</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN >; if the subquery returns no rows, the result of <TT CLASS="TOKEN" >EXISTS</TT > is <SPAN CLASS="QUOTE" >"false"</SPAN >. </P ><P > The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery. </P ><P > The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion. It is unwise to write a subquery that has side effects (such as calling sequence functions); whether the side effects occur might be unpredictable. </P ><P > Since the result depends only on whether any rows are returned, and not on the contents of those rows, the output list of the subquery is normally unimportant. A common coding convention is to write all <TT CLASS="LITERAL" >EXISTS</TT > tests in the form <TT CLASS="LITERAL" >EXISTS(SELECT 1 WHERE ...)</TT >. There are exceptions to this rule however, such as subqueries that use <TT CLASS="TOKEN" >INTERSECT</TT >. </P ><P > This simple example is like an inner join on <TT CLASS="LITERAL" >col2</TT >, but it produces at most one output row for each <TT CLASS="LITERAL" >tab1</TT > row, even if there are several matching <TT CLASS="LITERAL" >tab2</TT > rows: </P><PRE CLASS="SCREEN" >SELECT col1 FROM tab1 WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);</PRE ><P> </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="FUNCTIONS-SUBQUERY-IN" >9.22.2. <TT CLASS="LITERAL" >IN</TT ></A ></H2 ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IN (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > 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 <TT CLASS="TOKEN" >IN</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN > if any equal subquery row is found. The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if no equal row is found (including the case where the subquery returns no rows). </P ><P > Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the <TT CLASS="TOKEN" >IN</TT > construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values. </P ><P > As with <TT CLASS="TOKEN" >EXISTS</TT >, it's unwise to assume that the subquery will be evaluated completely. </P ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >row_constructor</I ></TT > IN (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > The left-hand side of this form of <TT CLASS="TOKEN" >IN</TT > is a row constructor, as described in <A HREF="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" >Section 4.2.13</A >. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of <TT CLASS="TOKEN" >IN</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN > if any equal subquery row is found. The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if no equal row is found (including the case where the subquery returns no rows). </P ><P > 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 that row comparison is unknown (null). If all the per-row results are either unequal or null, with at least one null, then the result of <TT CLASS="TOKEN" >IN</TT > is null. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="FUNCTIONS-SUBQUERY-NOTIN" >9.22.3. <TT CLASS="LITERAL" >NOT IN</TT ></A ></H2 ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > NOT IN (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > 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 <TT CLASS="TOKEN" >NOT IN</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN > if only unequal subquery rows are found (including the case where the subquery returns no rows). The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if any equal row is found. </P ><P > Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the <TT CLASS="TOKEN" >NOT IN</TT > construct will be null, not true. This is in accordance with SQL's normal rules for Boolean combinations of null values. </P ><P > As with <TT CLASS="TOKEN" >EXISTS</TT >, it's unwise to assume that the subquery will be evaluated completely. </P ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >row_constructor</I ></TT > NOT IN (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > The left-hand side of this form of <TT CLASS="TOKEN" >NOT IN</TT > is a row constructor, as described in <A HREF="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" >Section 4.2.13</A >. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of <TT CLASS="TOKEN" >NOT IN</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN > if only unequal subquery rows are found (including the case where the subquery returns no rows). The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if any equal row is found. </P ><P > 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 that row comparison is unknown (null). If all the per-row results are either unequal or null, with at least one null, then the result of <TT CLASS="TOKEN" >NOT IN</TT > is null. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="FUNCTIONS-SUBQUERY-ANY-SOME" >9.22.4. <TT CLASS="LITERAL" >ANY</TT >/<TT CLASS="LITERAL" >SOME</TT ></A ></H2 ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > ANY (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >) <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > SOME (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > 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 <TT CLASS="REPLACEABLE" ><I >operator</I ></TT >, which must yield a Boolean result. The result of <TT CLASS="TOKEN" >ANY</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN > if any true result is obtained. The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if no true result is found (including the case where the subquery returns no rows). </P ><P > <TT CLASS="TOKEN" >SOME</TT > is a synonym for <TT CLASS="TOKEN" >ANY</TT >. <TT CLASS="TOKEN" >IN</TT > is equivalent to <TT CLASS="LITERAL" >= ANY</TT >. </P ><P > Note that if there are no successes and at least one right-hand row yields null for the operator's result, the result of the <TT CLASS="TOKEN" >ANY</TT > construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values. </P ><P > As with <TT CLASS="TOKEN" >EXISTS</TT >, it's unwise to assume that the subquery will be evaluated completely. </P ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >row_constructor</I ></TT > <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > ANY (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >) <TT CLASS="REPLACEABLE" ><I >row_constructor</I ></TT > <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > SOME (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > The left-hand side of this form of <TT CLASS="TOKEN" >ANY</TT > is a row constructor, as described in <A HREF="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" >Section 4.2.13</A >. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result, using the given <TT CLASS="REPLACEABLE" ><I >operator</I ></TT >. The result of <TT CLASS="TOKEN" >ANY</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN > if the comparison returns true for any subquery row. The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if the comparison returns false for every subquery row (including the case where the subquery returns no rows). The result is NULL if no comparison with a subquery row returns true, and at least one comparison returns NULL. </P ><P > See <A HREF="functions-comparisons.html#ROW-WISE-COMPARISON" >Section 9.23.5</A > for details about the meaning of a row constructor comparison. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="FUNCTIONS-SUBQUERY-ALL" >9.22.5. <TT CLASS="LITERAL" >ALL</TT ></A ></H2 ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > ALL (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > 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 <TT CLASS="REPLACEABLE" ><I >operator</I ></TT >, which must yield a Boolean result. The result of <TT CLASS="TOKEN" >ALL</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN > if all rows yield true (including the case where the subquery returns no rows). The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if any false result is found. The result is NULL if no comparison with a subquery row returns false, and at least one comparison returns NULL. </P ><P > <TT CLASS="TOKEN" >NOT IN</TT > is equivalent to <TT CLASS="LITERAL" ><> ALL</TT >. </P ><P > As with <TT CLASS="TOKEN" >EXISTS</TT >, it's unwise to assume that the subquery will be evaluated completely. </P ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >row_constructor</I ></TT > <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > ALL (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > The left-hand side of this form of <TT CLASS="TOKEN" >ALL</TT > is a row constructor, as described in <A HREF="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" >Section 4.2.13</A >. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result, using the given <TT CLASS="REPLACEABLE" ><I >operator</I ></TT >. The result of <TT CLASS="TOKEN" >ALL</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN > if the comparison returns true for all subquery rows (including the case where the subquery returns no rows). The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if the comparison returns false for any subquery row. The result is NULL if no comparison with a subquery row returns false, and at least one comparison returns NULL. </P ><P > See <A HREF="functions-comparisons.html#ROW-WISE-COMPARISON" >Section 9.23.5</A > for details about the meaning of a row constructor comparison. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN21185" >9.22.6. Single-row Comparison</A ></H2 ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >row_constructor</I ></TT > <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > The left-hand side is a row constructor, as described in <A HREF="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" >Section 4.2.13</A >. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. 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. </P ><P > See <A HREF="functions-comparisons.html#ROW-WISE-COMPARISON" >Section 9.23.5</A > for details about the meaning of a row constructor comparison. </P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="functions-window.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="functions-comparisons.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Window Functions</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Row and Array Comparisons</TD ></TR ></TABLE ></DIV ></BODY ></HTML >