I386LINUX/util/I386LINUX/doc/postgresql/html/queries-union.html

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
   2 <HTML
   3 ><HEAD
   4 ><TITLE
   5 >Combining Queries</TITLE
   6 ><META
   7 NAME="GENERATOR"
   8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
   9 REV="MADE"
  10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
  11 REL="HOME"
  12 TITLE="PostgreSQL 7.4.1 Documentation"
  13 HREF="index.html"><LINK
  14 REL="UP"
  15 TITLE="Queries"
  16 HREF="queries.html"><LINK
  17 REL="PREVIOUS"
  18 TITLE="Select Lists"
  19 HREF="queries-select-lists.html"><LINK
  20 REL="NEXT"
  21 TITLE="Sorting Rows"
  22 HREF="queries-order.html"><LINK
  23 REL="STYLESHEET"
  24 TYPE="text/css"
  25 HREF="stylesheet.css"><META
  26 NAME="creation"
  27 CONTENT="2003-12-22T03:48:47"></HEAD
  28 ><BODY
  29 CLASS="SECT1"
  30 ><DIV
  31 CLASS="NAVHEADER"
  32 ><TABLE
  33 SUMMARY="Header navigation table"
  34 WIDTH="100%"
  35 BORDER="0"
  36 CELLPADDING="0"
  37 CELLSPACING="0"
  38 ><TR
  39 ><TH
  40 COLSPAN="5"
  41 ALIGN="center"
  42 VALIGN="bottom"
  43 >PostgreSQL 7.4.1 Documentation</TH
  44 ></TR
  45 ><TR
  46 ><TD
  47 WIDTH="10%"
  48 ALIGN="left"
  49 VALIGN="top"
  50 ><A
  51 HREF="queries-select-lists.html"
  52 ACCESSKEY="P"
  53 >Prev</A
  54 ></TD
  55 ><TD
  56 WIDTH="10%"
  57 ALIGN="left"
  58 VALIGN="top"
  59 ><A
  60 HREF="queries.html"
  61 >Fast Backward</A
  62 ></TD
  63 ><TD
  64 WIDTH="60%"
  65 ALIGN="center"
  66 VALIGN="bottom"
  67 >Chapter 7. Queries</TD
  68 ><TD
  69 WIDTH="10%"
  70 ALIGN="right"
  71 VALIGN="top"
  72 ><A
  73 HREF="queries.html"
  74 >Fast Forward</A
  75 ></TD
  76 ><TD
  77 WIDTH="10%"
  78 ALIGN="right"
  79 VALIGN="top"
  80 ><A
  81 HREF="queries-order.html"
  82 ACCESSKEY="N"
  83 >Next</A
  84 ></TD
  85 ></TR
  86 ></TABLE
  87 ><HR
  88 ALIGN="LEFT"
  89 WIDTH="100%"></DIV
  90 ><DIV
  91 CLASS="SECT1"
  92 ><H1
  93 CLASS="SECT1"
  94 ><A
  95 NAME="QUERIES-UNION"
  96 >7.4. Combining Queries</A
  97 ></H1
  98 ><A
  99 NAME="AEN3011"
 100 ></A
 101 ><A
 102 NAME="AEN3013"
 103 ></A
 104 ><A
 105 NAME="AEN3015"
 106 ></A
 107 ><A
 108 NAME="AEN3017"
 109 ></A
 110 ><A
 111 NAME="AEN3019"
 112 ></A
 113 ><A
 114 NAME="AEN3021"
 115 ></A
 116 ><A
 117 NAME="AEN3023"
 118 ></A
 119 ><P
 120 >   The results of two queries can be combined using the set operations
 121    union, intersection, and difference.  The syntax is
 122 </P><PRE
 123 CLASS="SYNOPSIS"
 124 ><VAR
 125 CLASS="REPLACEABLE"
 126 >query1</VAR
 127 > UNION [<SPAN
 128 CLASS="OPTIONAL"
 129 >ALL</SPAN
 130 >] <VAR
 131 CLASS="REPLACEABLE"
 132 >query2</VAR
 133 >
 134 <VAR
 135 CLASS="REPLACEABLE"
 136 >query1</VAR
 137 > INTERSECT [<SPAN
 138 CLASS="OPTIONAL"
 139 >ALL</SPAN
 140 >] <VAR
 141 CLASS="REPLACEABLE"
 142 >query2</VAR
 143 >
 144 <VAR
 145 CLASS="REPLACEABLE"
 146 >query1</VAR
 147 > EXCEPT [<SPAN
 148 CLASS="OPTIONAL"
 149 >ALL</SPAN
 150 >] <VAR
 151 CLASS="REPLACEABLE"
 152 >query2</VAR
 153 ></PRE
 154 ><P>
 155    <VAR
 156 CLASS="REPLACEABLE"
 157 >query1</VAR
 158 > and
 159    <VAR
 160 CLASS="REPLACEABLE"
 161 >query2</VAR
 162 > are queries that can use any of
 163    the features discussed up to this point.  Set operations can also
 164    be nested and chained, for example
 165 </P><PRE
 166 CLASS="SYNOPSIS"
 167 ><VAR
 168 CLASS="REPLACEABLE"
 169 >query1</VAR
 170 > UNION <VAR
 171 CLASS="REPLACEABLE"
 172 >query2</VAR
 173 > UNION <VAR
 174 CLASS="REPLACEABLE"
 175 >query3</VAR
 176 ></PRE
 177 ><P>
 178    which really says
 179 </P><PRE
 180 CLASS="SYNOPSIS"
 181 >(<VAR
 182 CLASS="REPLACEABLE"
 183 >query1</VAR
 184 > UNION <VAR
 185 CLASS="REPLACEABLE"
 186 >query2</VAR
 187 >) UNION <VAR
 188 CLASS="REPLACEABLE"
 189 >query3</VAR
 190 ></PRE
 191 ><P>
 192   </P
 193 ><P
 194 >   <TT
 195 CLASS="LITERAL"
 196 >UNION</TT
 197 > effectively appends the result of
 198    <VAR
 199 CLASS="REPLACEABLE"
 200 >query2</VAR
 201 > to the result of
 202    <VAR
 203 CLASS="REPLACEABLE"
 204 >query1</VAR
 205 > (although there is no guarantee
 206    that this is the order in which the rows are actually returned).
 207    Furthermore, it eliminates all duplicate rows, in the sense of
 208    <TT
 209 CLASS="LITERAL"
 210 >DISTINCT</TT
 211 >, unless <TT
 212 CLASS="LITERAL"
 213 >UNION ALL</TT
 214 > is used.
 215   </P
 216 ><P
 217 >   <TT
 218 CLASS="LITERAL"
 219 >INTERSECT</TT
 220 > returns all rows that are both in the result
 221    of <VAR
 222 CLASS="REPLACEABLE"
 223 >query1</VAR
 224 > and in the result of
 225    <VAR
 226 CLASS="REPLACEABLE"
 227 >query2</VAR
 228 >.  Duplicate rows are eliminated
 229    unless <TT
 230 CLASS="LITERAL"
 231 >INTERSECT ALL</TT
 232 > is used.
 233   </P
 234 ><P
 235 >   <TT
 236 CLASS="LITERAL"
 237 >EXCEPT</TT
 238 > returns all rows that are in the result of
 239    <VAR
 240 CLASS="REPLACEABLE"
 241 >query1</VAR
 242 > but not in the result of
 243    <VAR
 244 CLASS="REPLACEABLE"
 245 >query2</VAR
 246 >.  (This is sometimes called the
 247    <I
 248 CLASS="FIRSTTERM"
 249 >difference</I
 250 > between two queries.)  Again, duplicates
 251    are eliminated unless <TT
 252 CLASS="LITERAL"
 253 >EXCEPT ALL</TT
 254 > is used.
 255   </P
 256 ><P
 257 >   In order to calculate the union, intersection, or difference of two
 258    queries, the two queries must be <SPAN
 259 CLASS="QUOTE"
 260 >"union compatible"</SPAN
 261 >,
 262    which means that they both return the same number of columns, and
 263    that the corresponding columns have compatible data types, as
 264    described in <A
 265 HREF="typeconv-union-case.html"
 266 >Section 10.5</A
 267 >.
 268   </P
 269 ></DIV
 270 ><DIV
 271 CLASS="NAVFOOTER"
 272 ><HR
 273 ALIGN="LEFT"
 274 WIDTH="100%"><TABLE
 275 SUMMARY="Footer navigation table"
 276 WIDTH="100%"
 277 BORDER="0"
 278 CELLPADDING="0"
 279 CELLSPACING="0"
 280 ><TR
 281 ><TD
 282 WIDTH="33%"
 283 ALIGN="left"
 284 VALIGN="top"
 285 ><A
 286 HREF="queries-select-lists.html"
 287 ACCESSKEY="P"
 288 >Prev</A
 289 ></TD
 290 ><TD
 291 WIDTH="34%"
 292 ALIGN="center"
 293 VALIGN="top"
 294 ><A
 295 HREF="index.html"
 296 ACCESSKEY="H"
 297 >Home</A
 298 ></TD
 299 ><TD
 300 WIDTH="33%"
 301 ALIGN="right"
 302 VALIGN="top"
 303 ><A
 304 HREF="queries-order.html"
 305 ACCESSKEY="N"
 306 >Next</A
 307 ></TD
 308 ></TR
 309 ><TR
 310 ><TD
 311 WIDTH="33%"
 312 ALIGN="left"
 313 VALIGN="top"
 314 >Select Lists</TD
 315 ><TD
 316 WIDTH="34%"
 317 ALIGN="center"
 318 VALIGN="top"
 319 ><A
 320 HREF="queries.html"
 321 ACCESSKEY="U"
 322 >Up</A
 323 ></TD
 324 ><TD
 325 WIDTH="33%"
 326 ALIGN="right"
 327 VALIGN="top"
 328 >Sorting Rows</TD
 329 ></TR
 330 ></TABLE
 331 ></DIV
 332 ></BODY
 333 ></HTML
 334 >