<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Row and Array Comparisons</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 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Functions and Operators" HREF="functions.html"><LINK REL="PREVIOUS" TITLE="Subquery Expressions" HREF="functions-subquery.html"><LINK REL="NEXT" TITLE="Set Returning Functions" HREF="functions-srf.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="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions-subquery.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 9. Functions and Operators</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="functions.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="functions-srf.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="FUNCTIONS-COMPARISONS" >9.17. Row and Array Comparisons</A ></H1 ><A NAME="AEN12721" ></A ><A NAME="AEN12723" ></A ><A NAME="AEN12725" ></A ><A NAME="AEN12727" ></A ><A NAME="AEN12729" ></A ><A NAME="AEN12731" ></A ><A NAME="AEN12734" ></A ><A NAME="AEN12736" ></A ><A NAME="AEN12738" ></A ><P > This section describes several specialized constructs for making multiple comparisons between groups of values. These forms are syntactically related to the subquery forms of the previous section, but do not involve subqueries. The forms involving array subexpressions are <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > extensions; the rest are <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM >-compliant. All of the expression forms documented in this section return Boolean (true/false) results. </P ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN12743" >9.17.1. <TT CLASS="LITERAL" >IN</TT ></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 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> </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 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 ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN12759" >9.17.2. <TT CLASS="LITERAL" >NOT IN</TT ></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 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> </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 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="AEN12781" >9.17.3. <TT CLASS="LITERAL" >ANY</TT >/<TT CLASS="LITERAL" >SOME</TT > (array)</A ></H2 ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > ANY (<TT CLASS="REPLACEABLE" ><I >array expression</I ></TT >) <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > SOME (<TT CLASS="REPLACEABLE" ><I >array expression</I ></TT >)</PRE ><P > The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array 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 array has zero elements). </P ><P > <TT CLASS="TOKEN" >SOME</TT > is a synonym for <TT CLASS="TOKEN" >ANY</TT >. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN12800" >9.17.4. <TT CLASS="LITERAL" >ALL</TT > (array)</A ></H2 ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > <TT CLASS="REPLACEABLE" ><I >operator</I ></TT > ALL (<TT CLASS="REPLACEABLE" ><I >array expression</I ></TT >)</PRE ><P > The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array 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 comparisons yield true (including the special case where the array has zero elements). The result is <SPAN CLASS="QUOTE" >"false"</SPAN > if any false result is found. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN12812" >9.17.5. Row-wise 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 >row_constructor</I ></TT ></PRE ><P > Each side is a row constructor, as described in <A HREF="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" >Section 4.2.11</A >. The two row values must have the same number of fields. Each side is evaluated and they are compared row-wise. 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 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 ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >row_constructor</I ></TT > IS DISTINCT FROM <TT CLASS="REPLACEABLE" ><I >row_constructor</I ></TT ></PRE ><P > This construct is similar to a <TT CLASS="LITERAL" ><></TT > row comparison, but it does not yield null for null inputs. Instead, any null value is considered unequal to (distinct from) any non-null value, and any two nulls are considered equal (not distinct). Thus the result will always be either true or false, never null. </P ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >row_constructor</I ></TT > IS NULL <TT CLASS="REPLACEABLE" ><I >row_constructor</I ></TT > IS NOT NULL</PRE ><P > These constructs test a row value for null or not null. A row value is considered not null if it has at least one field that is not 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-subquery.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-srf.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Subquery Expressions</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Set Returning Functions</TD ></TR ></TABLE ></DIV ></BODY ></HTML >
