Home • Explore • Blog • CI
☰



postgresql
19th chunk of `doc/src/sgml/queries.sgml`
06ce947d75f9a4e88b00005575888b2e3fbdd77e1e23d2120000000100000fa0
 and
    <xref linkend="syntax-window-functions"/>), these functions are evaluated
    after any grouping, aggregation, and <literal>HAVING</literal> filtering is
    performed.  That is, if the query uses any aggregates, <literal>GROUP
    BY</literal>, or <literal>HAVING</literal>, then the rows seen by the window functions
    are the group rows instead of the original table rows from
    <literal>FROM</literal>/<literal>WHERE</literal>.
   </para>

   <para>
    When multiple window functions are used, all the window functions having
    equivalent <literal>PARTITION BY</literal> and <literal>ORDER BY</literal>
    clauses in their window definitions are guaranteed to see the same
    ordering of the input rows, even if the <literal>ORDER BY</literal> does
    not uniquely determine the ordering.
    However, no guarantees are made about the evaluation of functions having
    different <literal>PARTITION BY</literal> or <literal>ORDER BY</literal> specifications.
    (In such cases a sort step is typically required between the passes of
    window function evaluations, and the sort is not guaranteed to preserve
    ordering of rows that its <literal>ORDER BY</literal> sees as equivalent.)
   </para>

   <para>
    Currently, window functions always require presorted data, and so the
    query output will be ordered according to one or another of the window
    functions' <literal>PARTITION BY</literal>/<literal>ORDER BY</literal> clauses.
    It is not recommended to rely on this, however.  Use an explicit
    top-level <literal>ORDER BY</literal> clause if you want to be sure the
    results are sorted in a particular way.
   </para>
  </sect2>
 </sect1>


 <sect1 id="queries-select-lists">
  <title>Select Lists</title>

  <indexterm>
   <primary>SELECT</primary>
   <secondary>select list</secondary>
  </indexterm>

  <para>
   As shown in the previous section,
   the table expression in the <command>SELECT</command> command
   constructs an intermediate virtual table by possibly combining
   tables, views, eliminating rows, grouping, etc.  This table is
   finally passed on to processing by the <firstterm>select list</firstterm>.  The select
   list determines which <emphasis>columns</emphasis> of the
   intermediate table are actually output.
  </para>

  <sect2 id="queries-select-list-items">
   <title>Select-List Items</title>

   <indexterm>
    <primary>*</primary>
   </indexterm>

   <para>
    The simplest kind of select list is <literal>*</literal> which
    emits all columns that the table expression produces.  Otherwise,
    a select list is a comma-separated list of value expressions (as
    defined in <xref linkend="sql-expressions"/>).  For instance, it
    could be a list of column names:
<programlisting>
SELECT a, b, c FROM ...
</programlisting>
     The columns names <literal>a</literal>, <literal>b</literal>, and <literal>c</literal>
     are either the actual names of the columns of tables referenced
     in the <literal>FROM</literal> clause, or the aliases given to them as
     explained in <xref linkend="queries-table-aliases"/>.  The name
     space available in the select list is the same as in the
     <literal>WHERE</literal> clause, unless grouping is used, in which case
     it is the same as in the <literal>HAVING</literal> clause.
   </para>

   <para>
    If more than one table has a column of the same name, the table
    name must also be given, as in:
<programlisting>
SELECT tbl1.a, tbl2.a, tbl1.b FROM ...
</programlisting>
    When working with multiple tables, it can also be useful to ask for
    all the columns of a particular table:
<programlisting>
SELECT tbl1.*, tbl2.a FROM ...
</programlisting>
    See <xref linkend="rowtypes-usage"/> for more about
    the <replaceable>table_name</replaceable><literal>.*</literal> notation.
   </para>

   <para>
    If an arbitrary value expression is used in the select list, it
    conceptually adds a new virtual column to the returned table.  The
Title: SQL Select Lists and Column Selection
Summary
This section explains the concept of select lists in SQL SELECT statements. It describes how the select list determines which columns from the intermediate table (created by the table expression) are included in the output. The text covers various ways to specify columns in the select list, including using '*' to select all columns, listing specific column names, and using table name qualifiers for disambiguation. It also mentions the possibility of using arbitrary value expressions in the select list, which effectively adds new virtual columns to the result. The section emphasizes the importance of column naming and aliasing, especially when working with multiple tables or using expressions. It also touches on the concept of name spaces in select lists and how they relate to WHERE and HAVING clauses in grouped queries.