<HTML ><HEAD ><TITLE >Subquery Expressions</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.73 "><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 7.3.2 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Functions and Operators" HREF="functions.html"><LINK REL="PREVIOUS" TITLE="Aggregate Functions" HREF="functions-aggregate.html"><LINK REL="NEXT" TITLE="Type Conversion" HREF="typeconv.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META NAME="creation" CONTENT="2003-02-03T20:17:34"></HEAD ><BODY CLASS="SECT1" BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#840084" ALINK="#0000FF" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="3" ALIGN="center" >PostgreSQL 7.3.2 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="bottom" ><A HREF="functions-aggregate.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="80%" ALIGN="center" VALIGN="bottom" >Chapter 6. Functions and Operators</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="bottom" ><A HREF="typeconv.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="FUNCTIONS-SUBQUERY" >6.15. Subquery Expressions</A ></H1 ><A NAME="AEN10465" ></A ><A NAME="AEN10467" ></A ><A NAME="AEN10469" ></A ><A NAME="AEN10471" ></A ><A NAME="AEN10473" ></A ><A NAME="AEN10475" ></A ><A NAME="AEN10477" ></A ><P > This section describes the <SPAN CLASS="ACRONYM" >SQL</SPAN >-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="AEN10482" >6.15.1. EXISTS</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 far 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 any side effects (such as calling sequence functions); whether the side effects occur or not may be difficult to predict. </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 uninteresting. 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 multiple 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="AEN10505" >6.15.2. IN (scalar form)</A ></H2 ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IN (<TT CLASS="REPLACEABLE" ><I >value</I ></TT >[<SPAN CLASS="OPTIONAL" >, ...</SPAN >])</PRE ><P > The right-hand side of this form of <TT CLASS="TOKEN" >IN</TT > is a parenthesized list of scalar expressions. The result is <SPAN CLASS="QUOTE" >"true"</SPAN > if the left-hand expression's result is equal to any of the right-hand expressions. This is a shorthand notation for </P><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > = <TT CLASS="REPLACEABLE" ><I >value1</I ></TT > OR <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > = <TT CLASS="REPLACEABLE" ><I >value2</I ></TT > OR ...</PRE ><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 expression 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 ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > This form of <TT CLASS="TOKEN" >IN</TT > is not truly a subquery expression, but it seems best to document it in the same place as subquery <TT CLASS="TOKEN" >IN</TT >. </P ></BLOCKQUOTE ></DIV ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN10524" >6.15.3. IN (subquery form)</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 of this form of <TT CLASS="TOKEN" >IN</TT > 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 special 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 >expression</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > ...</SPAN >]) IN (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > The right-hand side of this form of <TT CLASS="TOKEN" >IN</TT > is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand list. 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 special case where the subquery returns no rows). </P ><P > As usual, null values in the expressions or subquery 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 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="AEN10550" >6.15.4. NOT IN (scalar form)</A ></H2 ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > NOT IN (<TT CLASS="REPLACEABLE" ><I >value</I ></TT >[<SPAN CLASS="OPTIONAL" >, ...</SPAN >])</PRE ><P > The right-hand side of this form of <TT CLASS="TOKEN" >NOT IN</TT > is a parenthesized list of scalar expressions. The result is <SPAN CLASS="QUOTE" >"true"</SPAN > if the left-hand expression's result is unequal to all of the right-hand expressions. This is a shorthand notation for </P><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > <> <TT CLASS="REPLACEABLE" ><I >value1</I ></TT > AND <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > <> <TT CLASS="REPLACEABLE" ><I >value2</I ></TT > AND ...</PRE ><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 expression yields null, the result of the <TT CLASS="TOKEN" >NOT IN</TT > 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. </P ><DIV CLASS="TIP" ><BLOCKQUOTE CLASS="TIP" ><P ><B >Tip: </B > <TT CLASS="LITERAL" >x NOT IN y</TT > is equivalent to <TT CLASS="LITERAL" >NOT (x IN y)</TT > in all cases. However, null values are much more likely to trip up the novice when working with <TT CLASS="TOKEN" >NOT IN</TT > than when working with <TT CLASS="TOKEN" >IN</TT >. It's best to express your condition positively if possible. </P ></BLOCKQUOTE ></DIV ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN10571" >6.15.5. NOT IN (subquery form)</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 of this form of <TT CLASS="TOKEN" >NOT IN</TT > 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 special 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 >expression</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > ...</SPAN >]) NOT IN (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > The right-hand side of this form of <TT CLASS="TOKEN" >NOT IN</TT > is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand list. 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 special 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 expressions or subquery 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 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="AEN10597" >6.15.6. ANY/SOME</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 of this form of <TT CLASS="TOKEN" >ANY</TT > 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 special 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 >expression</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > ...</SPAN >]) <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > ANY (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >) (<TT CLASS="REPLACEABLE" ><I >expression</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > ...</SPAN >]) <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > SOME (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >)</PRE ><P > The right-hand side of this form of <TT CLASS="TOKEN" >ANY</TT > is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand list. 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 >. Presently, only <TT CLASS="LITERAL" >=</TT > and <TT CLASS="LITERAL" ><></TT > operators are allowed in row-wise <TT CLASS="TOKEN" >ANY</TT > queries. The result of <TT CLASS="TOKEN" >ANY</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN > if any equal or unequal row is found, respectively. The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if no such row is found (including the special case where the subquery returns no rows). </P ><P > As usual, null values in the expressions or subquery 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 there is at least one null row result, then the result of <TT CLASS="TOKEN" >ANY</TT > cannot be false; it will be true or null. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN10643" >6.15.7. ALL</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 of this form of <TT CLASS="TOKEN" >ALL</TT > 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 special case where the subquery returns no rows). The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if any false result is found. </P ><P > <TT CLASS="TOKEN" >NOT IN</TT > is equivalent to <TT CLASS="LITERAL" ><> ALL</TT >. </P ><P > Note that if there are no failures but at least one right-hand row yields null for the operator's result, the result of the <TT CLASS="TOKEN" >ALL</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 >expression</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > ...</SPAN >]) <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > ALL (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >) </PRE ><P > The right-hand side of this form of <TT CLASS="TOKEN" >ALL</TT > is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand list. 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 >. Presently, only <TT CLASS="LITERAL" >=</TT > and <TT CLASS="LITERAL" ><></TT > operators are allowed in row-wise <TT CLASS="TOKEN" >ALL</TT > queries. The result of <TT CLASS="TOKEN" >ALL</TT > is <SPAN CLASS="QUOTE" >"true"</SPAN > if all subquery rows are equal or unequal, respectively (including the special case where the subquery returns no rows). The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if any row is found to be unequal or equal, respectively. </P ><P > As usual, null values in the expressions or subquery 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 there is at least one null row result, then the result of <TT CLASS="TOKEN" >ALL</TT > cannot be true; it will be false or null. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN10679" >6.15.8. Row-wise Comparison</A ></H2 ><PRE CLASS="SYNOPSIS" >(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > ...</SPAN >]) <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > (<TT CLASS="REPLACEABLE" ><I >subquery</I ></TT >) (<TT CLASS="REPLACEABLE" ><I >expression</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > ...</SPAN >]) <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > (<TT CLASS="REPLACEABLE" ><I >expression</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > ...</SPAN >]) </PRE ><P > 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 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. Presently, only <TT CLASS="LITERAL" >=</TT > and <TT CLASS="LITERAL" ><></TT > operators are allowed in row-wise comparisons. The result is <SPAN CLASS="QUOTE" >"true"</SPAN > if the two rows are equal or unequal, respectively. </P ><P > As usual, null values in the expressions or subquery 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). </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-aggregate.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="typeconv.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Aggregate 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" >Type Conversion</TD ></TR ></TABLE ></DIV ></BODY ></HTML >