<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="select">
      </a>
      15.2.13 SELECT Statement
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="select-into.html">
       15.2.13.1 SELECT ... INTO Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="join.html">
       15.2.13.2 JOIN Clause
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045181044608">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97027829"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span>
    <span class="token punctuation">[</span><span class="token keyword">ALL</span> <span class="token operator">|</span> <span class="token keyword">DISTINCT</span> <span class="token operator">|</span> <span class="token keyword">DISTINCTROW</span> <span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">HIGH_PRIORITY</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">STRAIGHT_JOIN</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">SQL_SMALL_RESULT</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">SQL_BIG_RESULT</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">SQL_BUFFER_RESULT</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">SQL_NO_CACHE</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">SQL_CALC_FOUND_ROWS</span><span class="token punctuation">]</span>
    <em class="replaceable">select_expr</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">select_expr</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
    <span class="token punctuation">[</span><em class="replaceable">into_option</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">FROM</span> <em class="replaceable">table_references</em>
      <span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <em class="replaceable">partition_list</em><span class="token punctuation">]</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">WHERE</span> <em class="replaceable">where_condition</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">GROUP</span> <span class="token keyword">BY</span> {<em class="replaceable">col_name</em> <span class="token operator">|</span> <em class="replaceable">expr</em> <span class="token operator">|</span> <em class="replaceable">position</em>}<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">[</span><span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">]</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">HAVING</span> <em class="replaceable">where_condition</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">WINDOW</span> <em class="replaceable">window_name</em> <span class="token keyword">AS</span> <span class="token punctuation">(</span><em class="replaceable">window_spec</em><span class="token punctuation">)</span>
        <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">window_name</em> <span class="token keyword">AS</span> <span class="token punctuation">(</span><em class="replaceable">window_spec</em><span class="token punctuation">)</span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> {<em class="replaceable">col_name</em> <span class="token operator">|</span> <em class="replaceable">expr</em> <span class="token operator">|</span> <em class="replaceable">position</em>}
      <span class="token punctuation">[</span><span class="token keyword">ASC</span> <span class="token operator">|</span> <span class="token keyword">DESC</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">[</span><span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">]</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">LIMIT</span> {<span class="token punctuation">[</span><span class="token keyword"><em class="replaceable">offset</em></span><span class="token punctuation">,</span><span class="token punctuation">]</span> <span class="token keyword"><em class="replaceable">row_count</em></span> <span class="token operator">|</span> <span class="token keyword"><em class="replaceable">row_count</em></span> <span class="token keyword">OFFSET</span> <span class="token keyword"><em class="replaceable">offset</em></span>}<span class="token punctuation">]</span>
    <span class="token punctuation">[</span><em class="replaceable">into_option</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">FOR</span> {<span class="token keyword">UPDATE</span> <span class="token operator">|</span> <span class="token keyword">SHARE</span>}
        <span class="token punctuation">[</span><span class="token keyword">OF</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">tbl_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">NOWAIT</span> <span class="token operator">|</span> <span class="token keyword">SKIP</span> <span class="token keyword">LOCKED</span><span class="token punctuation">]</span>
      <span class="token operator">|</span> <span class="token keyword">LOCK</span> <span class="token keyword">IN</span> <span class="token keyword">SHARE</span> <span class="token keyword">MODE</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><em class="replaceable">into_option</em><span class="token punctuation">]</span>

<em class="replaceable">into_option</em>: {
    <span class="token keyword">INTO</span> <span class="token keyword">OUTFILE</span> <span class="token string">'<em class="replaceable">file_name</em>'</span>
        <span class="token punctuation">[</span><span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">]</span>
        <em class="replaceable">export_options</em>
  <span class="token operator">|</span> <span class="token keyword">INTO</span> <span class="token keyword">DUMPFILE</span> <span class="token string">'<em class="replaceable">file_name</em>'</span>
  <span class="token operator">|</span> <span class="token keyword">INTO</span> <em class="replaceable">var_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">var_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
}</code></pre>
  </div>
  <p>
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   is used to retrieve rows
      selected from one or more tables, and can include
   <a class="link" href="union.html" title="15.2.18 UNION Clause">
    <code class="literal">
     UNION
    </code>
   </a>
   operations and subqueries.
   <a class="link" href="intersect.html" title="15.2.8 INTERSECT Clause">
    <code class="literal">
     INTERSECT
    </code>
   </a>
   and
   <a class="link" href="except.html" title="15.2.4 EXCEPT Clause">
    <code class="literal">
     EXCEPT
    </code>
   </a>
   operations are also
      supported. The
   <code class="literal">
    UNION
   </code>
   ,
   <code class="literal">
    INTERSECT
   </code>
   , and
   <code class="literal">
    EXCEPT
   </code>
   operators are described in more detail later in this section. See
      also
   <a class="xref" href="subqueries.html" title="15.2.15 Subqueries">
    Section 15.2.15, “Subqueries”
   </a>
   .
  </p>
  <p>
   A
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement can start with a
   <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
    <code class="literal">
     WITH
    </code>
   </a>
   clause to define common table
      expressions accessible within the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   . See
   <a class="xref" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
    Section 15.2.20, “WITH (Common Table Expressions)”
   </a>
   .
  </p>
  <p>
   The most commonly used clauses of
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements are these:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Each
      <em class="replaceable">
       <code>
        select_expr
       </code>
      </em>
      indicates a column
          that you want to retrieve. There must be at least one
      <em class="replaceable">
       <code>
        select_expr
       </code>
      </em>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        table_references
       </code>
      </em>
      indicates the
          table or tables from which to retrieve rows. Its syntax is
          described in
      <a class="xref" href="join.html" title="15.2.13.2 JOIN Clause">
       Section 15.2.13.2, “JOIN Clause”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SELECT
      </code>
      supports explicit partition
          selection using the
      <code class="literal">
       PARTITION
      </code>
      clause with a
          list of partitions or subpartitions (or both) following the
          name of the table in a
      <em class="replaceable">
       <code>
        table_reference
       </code>
      </em>
      (see
      <a class="xref" href="join.html" title="15.2.13.2 JOIN Clause">
       Section 15.2.13.2, “JOIN Clause”
      </a>
      ). In this case, rows are selected only
          from the partitions listed, and any other partitions of the
          table are ignored. For more information and examples, see
      <a class="xref" href="partitioning-selection.html" title="26.5 Partition Selection">
       Section 26.5, “Partition Selection”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       WHERE
      </code>
      clause, if given, indicates the
          condition or conditions that rows must satisfy to be selected.
      <em class="replaceable">
       <code>
        where_condition
       </code>
      </em>
      is an expression
          that evaluates to true for each row to be selected. The
          statement selects all rows if there is no
      <code class="literal">
       WHERE
      </code>
      clause.
     </p>
     <p>
      In the
      <code class="literal">
       WHERE
      </code>
      expression, you can use any of
          the functions and operators that MySQL supports, except for
          aggregate (group) functions. See
      <a class="xref" href="expressions.html" title="11.5 Expressions">
       Section 11.5, “Expressions”
      </a>
      , and
      <a class="xref" href="functions.html" title="Chapter 14 Functions and Operators">
       Chapter 14,
       <i>
        Functions and Operators
       </i>
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   can also be used to retrieve
      rows computed without reference to any table.
  </p>
  <p>
   For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa80652143"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">2</span></code></pre>
  </div>
  <p>
   <a class="indexterm" name="idm46045180989136">
   </a>
   You are permitted to specify
   <code class="literal">
    DUAL
   </code>
   as a dummy
      table name in situations where no tables are referenced:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59583673"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">FROM</span> <span class="token keyword">DUAL</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">2</span></code></pre>
  </div>
  <p>
   <code class="literal">
    DUAL
   </code>
   is purely for the convenience of people
      who require that all
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements should have
   <code class="literal">
    FROM
   </code>
   and possibly other
      clauses. MySQL may ignore the clauses. MySQL does not require
   <code class="literal">
    FROM DUAL
   </code>
   if no tables are referenced.
  </p>
  <p>
   In general, clauses used must be given in exactly the order shown
      in the syntax description. For example, a
   <code class="literal">
    HAVING
   </code>
   clause must come after any
   <code class="literal">
    GROUP BY
   </code>
   clause and before any
   <code class="literal">
    ORDER
      BY
   </code>
   clause. The
   <code class="literal">
    INTO
   </code>
   clause, if
      present, can appear in any position indicated by the syntax
      description, but within a given statement can appear only once,
      not in multiple positions. For more information about
   <code class="literal">
    INTO
   </code>
   , see
   <a class="xref" href="select-into.html" title="15.2.13.1 SELECT ... INTO Statement">
    Section 15.2.13.1, “SELECT ... INTO Statement”
   </a>
   .
  </p>
  <p>
   The list of
   <em class="replaceable">
    <code>
     select_expr
    </code>
   </em>
   terms comprises
      the select list that indicates which columns to retrieve. Terms
      specify a column or expression or can use
   <code class="literal">
    *
   </code>
   -shorthand:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A select list consisting only of a single unqualified
      <code class="literal">
       *
      </code>
      can be used as shorthand to select all
          columns from all tables:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa95357462"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> t2 <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       .*
      </code>
      can
          be used as a qualified shorthand to select all columns from
          the named table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa48629154"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> t1<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">,</span> t2<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> t2 <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      If a table has invisible columns,
      <code class="literal">
       *
      </code>
      and
      <code class="literal">
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       .*
      </code>
      do
          not include them. To be included, invisible columns must be
          referenced explicitly.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use of an unqualified
      <code class="literal">
       *
      </code>
      with other items in
          the select list may produce a parse error. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa49923706"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1</code></pre>
     </div>
     <p>
      To avoid this problem, use a qualified
      <code class="literal">
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       .*
      </code>
      reference:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa64800390"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> t1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t1</code></pre>
     </div>
     <p>
      Use qualified
      <code class="literal">
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       .*
      </code>
      references for each table in the select list:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa25879"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>score<span class="token punctuation">)</span><span class="token punctuation">,</span> t1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The following list provides additional information about other
   <code class="literal">
    SELECT
   </code>
   clauses:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045180956688">
      </a>
      <a class="indexterm" name="idm46045180955200">
      </a>
      A
      <em class="replaceable">
       <code>
        select_expr
       </code>
      </em>
      can be given an alias
          using
      <code class="literal">
       AS
       <em class="replaceable">
        <code>
         alias_name
        </code>
       </em>
      </code>
      . The alias is
          used as the expression's column name and can be used in
      <code class="literal">
       GROUP BY
      </code>
      ,
      <code class="literal">
       ORDER BY
      </code>
      , or
      <code class="literal">
       HAVING
      </code>
      clauses. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37884424"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>last_name<span class="token punctuation">,</span><span class="token string">', '</span><span class="token punctuation">,</span>first_name<span class="token punctuation">)</span> <span class="token keyword">AS</span> full_name
  <span class="token keyword">FROM</span> mytable <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> full_name<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The
      <code class="literal">
       AS
      </code>
      keyword is optional when aliasing a
      <em class="replaceable">
       <code>
        select_expr
       </code>
      </em>
      with an identifier. The
          preceding example could have been written like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa9397775"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>last_name<span class="token punctuation">,</span><span class="token string">', '</span><span class="token punctuation">,</span>first_name<span class="token punctuation">)</span> full_name
  <span class="token keyword">FROM</span> mytable <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> full_name<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      However, because the
      <code class="literal">
       AS
      </code>
      is optional, a
          subtle problem can occur if you forget the comma between two
      <em class="replaceable">
       <code>
        select_expr
       </code>
      </em>
      expressions: MySQL
          interprets the second as an alias name. For example, in the
          following statement,
      <code class="literal">
       columnb
      </code>
      is treated as
          an alias name:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa28183320"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> columna columnb <span class="token keyword">FROM</span> mytable<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For this reason, it is good practice to be in the habit of
          using
      <code class="literal">
       AS
      </code>
      explicitly when specifying column
          aliases.
     </p>
     <p>
      It is not permissible to refer to a column alias in a
      <code class="literal">
       WHERE
      </code>
      clause, because the column value
          might not yet be determined when the
      <code class="literal">
       WHERE
      </code>
      clause is executed. See
      <a class="xref" href="problems-with-alias.html" title="B.3.4.4 Problems with Column Aliases">
       Section B.3.4.4, “Problems with Column Aliases”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045180938496">
      </a>
      <a class="indexterm" name="idm46045180937424">
      </a>
      The
      <code class="literal">
       FROM
       <em class="replaceable">
        <code>
         table_references
        </code>
       </em>
      </code>
      clause
          indicates the table or tables from which to retrieve rows. If
          you name more than one table, you are performing a join. For
          information on join syntax, see
      <a class="xref" href="join.html" title="15.2.13.2 JOIN Clause">
       Section 15.2.13.2, “JOIN Clause”
      </a>
      . For
          each table specified, you can optionally specify an alias.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa73439598"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">tbl_name</em> <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token keyword">AS</span><span class="token punctuation">]</span> <em class="replaceable">alias</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">index_hint</em><span class="token punctuation">]</span></code></pre>
     </div>
     <a class="indexterm" name="idm46045180932256">
     </a>
     <a class="indexterm" name="idm46045180931184">
     </a>
     <p>
      The use of index hints provides the optimizer with information
          about how to choose indexes during query processing. For a
          description of the syntax for specifying these hints, see
      <a class="xref" href="index-hints.html" title="10.9.4 Index Hints">
       Section 10.9.4, “Index Hints”
      </a>
      .
     </p>
     <p>
      You can use
      <code class="literal">
       SET
          max_seeks_for_key=
       <em class="replaceable">
        <code>
         value
        </code>
       </em>
      </code>
      as an alternative way to force MySQL to prefer key scans
          instead of table scans. See
      <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
       Section 7.1.8, “Server System Variables”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You can refer to a table within the default database as
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
      , or as
      <em class="replaceable">
       <code>
        db_name
       </code>
      </em>
      .
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
      to specify a database explicitly. You can refer to a column as
      <em class="replaceable">
       <code>
        col_name
       </code>
      </em>
      ,
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
      .
      <em class="replaceable">
       <code>
        col_name
       </code>
      </em>
      ,
          or
      <em class="replaceable">
       <code>
        db_name
       </code>
      </em>
      .
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
      .
      <em class="replaceable">
       <code>
        col_name
       </code>
      </em>
      .
          You need not specify a
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
      or
      <em class="replaceable">
       <code>
        db_name
       </code>
      </em>
      .
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
      prefix for a column reference unless the reference would be
          ambiguous. See
      <a class="xref" href="identifier-qualifiers.html" title="11.2.2 Identifier Qualifiers">
       Section 11.2.2, “Identifier Qualifiers”
      </a>
      , for
          examples of ambiguity that require the more explicit column
          reference forms.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045180918496">
      </a>
      <a class="indexterm" name="idm46045180917040">
      </a>
      A table reference can be aliased using
      <code class="literal">
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       AS
       <em class="replaceable">
        <code>
         alias_name
        </code>
       </em>
      </code>
      or
      <em class="replaceable">
       <code>
        tbl_name alias_name
       </code>
      </em>
      . These
          statements are equivalent:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43824691"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> t1<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span> t2<span class="token punctuation">.</span>salary <span class="token keyword">FROM</span> employee <span class="token keyword">AS</span> t1<span class="token punctuation">,</span> info <span class="token keyword">AS</span> t2
  <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span><span class="token keyword">name</span> <span class="token operator">=</span> t2<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> t1<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span> t2<span class="token punctuation">.</span>salary <span class="token keyword">FROM</span> employee t1<span class="token punctuation">,</span> info t2
  <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span><span class="token keyword">name</span> <span class="token operator">=</span> t2<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045180912272">
      </a>
      Columns selected for output can be referred to in
      <code class="literal">
       ORDER BY
      </code>
      and
      <code class="literal">
       GROUP BY
      </code>
      clauses using column names, column aliases, or column
          positions. Column positions are integers and begin with 1:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89768865"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> college<span class="token punctuation">,</span> region<span class="token punctuation">,</span> seed <span class="token keyword">FROM</span> tournament
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> region<span class="token punctuation">,</span> seed<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> college<span class="token punctuation">,</span> region <span class="token keyword">AS</span> r<span class="token punctuation">,</span> seed <span class="token keyword">AS</span> s <span class="token keyword">FROM</span> tournament
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> r<span class="token punctuation">,</span> s<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> college<span class="token punctuation">,</span> region<span class="token punctuation">,</span> seed <span class="token keyword">FROM</span> tournament
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      To sort in reverse order, add the
      <code class="literal">
       DESC
      </code>
      (descending) keyword to the name of the column in the
      <code class="literal">
       ORDER BY
      </code>
      clause that you are sorting by.
          The default is ascending order; this can be specified
          explicitly using the
      <code class="literal">
       ASC
      </code>
      keyword.
     </p>
     <p>
      If
      <code class="literal">
       ORDER BY
      </code>
      occurs within a parenthesized
          query expression and also is applied in the outer query, the
          results are undefined and may change in a future version of
          MySQL.
     </p>
     <p>
      Use of column positions is deprecated because the syntax has
          been removed from the SQL standard.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045180903056">
      </a>
      <a class="indexterm" name="idm46045180901568">
      </a>
      When you use
      <code class="literal">
       ORDER BY
      </code>
      or
      <code class="literal">
       GROUP
          BY
      </code>
      to sort a column in a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      , the server sorts values
          using only the initial number of bytes indicated by the
      <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
       <code class="literal">
        max_sort_length
       </code>
      </a>
      system
          variable.
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL extends the use of
      <code class="literal">
       GROUP BY
      </code>
      to permit
          selecting fields that are not mentioned in the
      <code class="literal">
       GROUP
          BY
      </code>
      clause. If you are not getting the results that
          you expect from your query, please read the description of
      <code class="literal">
       GROUP BY
      </code>
      found in
      <a class="xref" href="aggregate-functions-and-modifiers.html" title="14.19 Aggregate Functions">
       Section 14.19, “Aggregate Functions”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       HAVING
      </code>
      clause, like the
      <code class="literal">
       WHERE
      </code>
      clause, specifies selection
          conditions. The
      <code class="literal">
       WHERE
      </code>
      clause specifies
          conditions on columns in the select list, but cannot refer to
          aggregate functions. The
      <code class="literal">
       HAVING
      </code>
      clause
          specifies conditions on groups, typically formed by the
      <code class="literal">
       GROUP BY
      </code>
      clause. The query result includes
          only groups satisfying the
      <code class="literal">
       HAVING
      </code>
      conditions. (If no
      <code class="literal">
       GROUP BY
      </code>
      is present, all
          rows implicitly form a single aggregate group.)
     </p>
     <p>
      The
      <code class="literal">
       HAVING
      </code>
      clause is applied nearly last,
          just before items are sent to the client, with no
          optimization. (
      <code class="literal">
       LIMIT
      </code>
      is applied after
      <code class="literal">
       HAVING
      </code>
      .)
     </p>
     <a class="indexterm" name="idm46045180883152">
     </a>
     <p>
      The SQL standard requires that
      <code class="literal">
       HAVING
      </code>
      must
          reference only columns in the
      <code class="literal">
       GROUP BY
      </code>
      clause or columns used in aggregate functions. However, MySQL
          supports an extension to this behavior, and permits
      <code class="literal">
       HAVING
      </code>
      to refer to columns in the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      list and columns in
          outer subqueries as well.
     </p>
     <p>
      If the
      <code class="literal">
       HAVING
      </code>
      clause refers to a column
          that is ambiguous, a warning occurs. In the following
          statement,
      <code class="literal">
       col2
      </code>
      is ambiguous because it is
          used as both an alias and a column name:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa16416404"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>col1<span class="token punctuation">)</span> <span class="token keyword">AS</span> col2 <span class="token keyword">FROM</span> t <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> col2 <span class="token keyword">HAVING</span> col2 <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Preference is given to standard SQL behavior, so if a
      <code class="literal">
       HAVING
      </code>
      column name is used both in
      <code class="literal">
       GROUP BY
      </code>
      and as an aliased column in the
          select column list, preference is given to the column in the
      <code class="literal">
       GROUP BY
      </code>
      column.
     </p>
    </li>
    <li class="listitem">
     <p>
      Do not use
      <code class="literal">
       HAVING
      </code>
      for items that should be
          in the
      <code class="literal">
       WHERE
      </code>
      clause. For example, do not
          write the following:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa72263128"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <em class="replaceable">col_name</em> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">HAVING</span> <em class="replaceable">col_name</em> <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Write this instead:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa14787361"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <em class="replaceable">col_name</em> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> <em class="replaceable">col_name</em> <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       HAVING
      </code>
      clause can refer to aggregate
          functions, which the
      <code class="literal">
       WHERE
      </code>
      clause cannot:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11215538"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token keyword">user</span><span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">FROM</span> users
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">user</span> <span class="token keyword">HAVING</span> <span class="token function">MAX</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token operator">&gt;</span> <span class="token number">10</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      (This did not work in some older versions of MySQL.)
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL permits duplicate column names. That is, there can be
          more than one
      <em class="replaceable">
       <code>
        select_expr
       </code>
      </em>
      with the
          same name. This is an extension to standard SQL. Because MySQL
          also permits
      <code class="literal">
       GROUP BY
      </code>
      and
      <code class="literal">
       HAVING
      </code>
      to refer to
      <em class="replaceable">
       <code>
        select_expr
       </code>
      </em>
      values, this can result
          in an ambiguity:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa39863627"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token number">12</span> <span class="token keyword">AS</span> a<span class="token punctuation">,</span> a <span class="token keyword">FROM</span> t <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> a<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      In that statement, both columns have the name
      <code class="literal">
       a
      </code>
      . To ensure that the correct column is
          used for grouping, use different names for each
      <em class="replaceable">
       <code>
        select_expr
       </code>
      </em>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       WINDOW
      </code>
      clause, if present, defines
          named windows that can be referred to by window functions. For
          details, see
      <a class="xref" href="window-functions-named-windows.html" title="14.20.4 Named Windows">
       Section 14.20.4, “Named Windows”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL resolves unqualified column or alias references in
      <code class="literal">
       ORDER BY
      </code>
      clauses by searching in the
      <em class="replaceable">
       <code>
        select_expr
       </code>
      </em>
      values, then in the
          columns of the tables in the
      <code class="literal">
       FROM
      </code>
      clause.
          For
      <code class="literal">
       GROUP BY
      </code>
      or
      <code class="literal">
       HAVING
      </code>
      clauses, it searches the
      <code class="literal">
       FROM
      </code>
      clause before
          searching in the
      <em class="replaceable">
       <code>
        select_expr
       </code>
      </em>
      values. (For
      <code class="literal">
       GROUP BY
      </code>
      and
      <code class="literal">
       HAVING
      </code>
      , this differs from the pre-MySQL 5.0
          behavior that used the same rules as for
      <code class="literal">
       ORDER
          BY
      </code>
      .)
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045180844432">
      </a>
      The
      <code class="literal">
       LIMIT
      </code>
      clause can be used to constrain
          the number of rows returned by the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement.
      <code class="literal">
       LIMIT
      </code>
      takes one or two numeric arguments,
          which must both be nonnegative integer constants, with these
          exceptions:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Within prepared statements,
         <code class="literal">
          LIMIT
         </code>
         parameters can be specified using
         <code class="literal">
          ?
         </code>
         placeholder markers.
        </p>
       </li>
       <li class="listitem">
        <p>
         Within stored programs,
         <code class="literal">
          LIMIT
         </code>
         parameters can be specified using integer-valued routine
              parameters or local variables.
        </p>
       </li>
      </ul>
     </div>
     <p>
      With two arguments, the first argument specifies the offset of
          the first row to return, and the second specifies the maximum
          number of rows to return. The offset of the initial row is 0
          (not 1):
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa42466129"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tbl <span class="token keyword">LIMIT</span> <span class="token number">5</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">;</span>  <span class="token comment" spellcheck="true"># Retrieve rows 6-15</span></code></pre>
     </div>
     <p>
      To retrieve all rows from a certain offset up to the end of
          the result set, you can use some large number for the second
          parameter. This statement retrieves all rows from the 96th row
          to the last:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa62037368"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tbl <span class="token keyword">LIMIT</span> <span class="token number">95</span><span class="token punctuation">,</span><span class="token number">18446744073709551615</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      With one argument, the value specifies the number of rows to
          return from the beginning of the result set:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa11504561"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tbl <span class="token keyword">LIMIT</span> <span class="token number">5</span><span class="token punctuation">;</span>     <span class="token comment" spellcheck="true"># Retrieve first 5 rows</span></code></pre>
     </div>
     <p>
      In other words,
      <code class="literal">
       LIMIT
       <em class="replaceable">
        <code>
         row_count
        </code>
       </em>
      </code>
      is equivalent
          to
      <code class="literal">
       LIMIT 0,
       <em class="replaceable">
        <code>
         row_count
        </code>
       </em>
      </code>
      .
     </p>
     <p>
      For prepared statements, you can use placeholders. The
          following statements return one row from the
      <code class="literal">
       tbl
      </code>
      table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76029112"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token variable">@a</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">PREPARE</span> STMT <span class="token keyword">FROM</span> <span class="token string">'SELECT * FROM tbl LIMIT ?'</span><span class="token punctuation">;</span>
<span class="token keyword">EXECUTE</span> STMT <span class="token keyword">USING</span> <span class="token variable">@a</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The following statements return the second to sixth rows from
          the
      <code class="literal">
       tbl
      </code>
      table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90654793"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token variable">@skip</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span> <span class="token keyword">SET</span> <span class="token variable">@numrows</span><span class="token operator">=</span><span class="token number">5</span><span class="token punctuation">;</span>
<span class="token keyword">PREPARE</span> STMT <span class="token keyword">FROM</span> <span class="token string">'SELECT * FROM tbl LIMIT ?, ?'</span><span class="token punctuation">;</span>
<span class="token keyword">EXECUTE</span> STMT <span class="token keyword">USING</span> <span class="token variable">@skip</span><span class="token punctuation">,</span> <span class="token variable">@numrows</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For compatibility with PostgreSQL, MySQL also supports the
      <code class="literal">
       LIMIT
       <em class="replaceable">
        <code>
         row_count
        </code>
       </em>
       OFFSET
       <em class="replaceable">
        <code>
         offset
        </code>
       </em>
      </code>
      syntax.
     </p>
     <p>
      If
      <code class="literal">
       LIMIT
      </code>
      occurs within a parenthesized
          query expression and also is applied in the outer query, the
          results are undefined and may change in a future version of
          MySQL.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="select-into.html" title="15.2.13.1 SELECT ... INTO Statement">
       <code class="literal">
        SELECT ...
          INTO
       </code>
      </a>
      form of
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      enables the query result to be written to a file or stored in
          variables. For more information, see
      <a class="xref" href="select-into.html" title="15.2.13.1 SELECT ... INTO Statement">
       Section 15.2.13.1, “SELECT ... INTO Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045180816432">
      </a>
      <a class="indexterm" name="idm46045180815360">
      </a>
      <a class="indexterm" name="idm46045180814288">
      </a>
      <a class="indexterm" name="idm46045180813216">
      </a>
      <a class="indexterm" name="idm46045180812144">
      </a>
      If you use
      <code class="literal">
       FOR UPDATE
      </code>
      with a storage engine
          that uses page or row locks, rows examined by the query are
          write-locked until the end of the current transaction.
     </p>
     <p>
      You cannot use
      <code class="literal">
       FOR UPDATE
      </code>
      as part of the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      in a statement such as
      <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
       <code class="literal">
        CREATE
          TABLE
        <em class="replaceable">
         <code>
          new_table
         </code>
        </em>
        SELECT ... FROM
        <em class="replaceable">
         <code>
          old_table
         </code>
        </em>
        ...
       </code>
      </a>
      . (If you
          attempt to do so, the statement is rejected with the error
      <span class="errortext">
       Can't update table
          '
       <em class="replaceable">
        <code>
         old_table
        </code>
       </em>
       ' while
          '
       <em class="replaceable">
        <code>
         new_table
        </code>
       </em>
       ' is being
          created
      </span>
      .)
     </p>
     <p>
      <code class="literal">
       FOR SHARE
      </code>
      and
      <code class="literal">
       LOCK IN SHARE
          MODE
      </code>
      set shared locks that permit other transactions
          to read the examined rows but not to update or delete them.
      <code class="literal">
       FOR SHARE
      </code>
      and
      <code class="literal">
       LOCK IN SHARE
          MODE
      </code>
      are equivalent. However,
      <code class="literal">
       FOR
          SHARE
      </code>
      , like
      <code class="literal">
       FOR UPDATE
      </code>
      , supports
      <code class="literal">
       NOWAIT
      </code>
      ,
      <code class="literal">
       SKIP LOCKED
      </code>
      , and
      <code class="literal">
       OF
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
      </code>
      options.
      <code class="literal">
       FOR SHARE
      </code>
      is a replacement for
      <code class="literal">
       LOCK IN SHARE MODE
      </code>
      , but
      <code class="literal">
       LOCK IN
          SHARE MODE
      </code>
      remains available for backward
          compatibility.
     </p>
     <p>
      <code class="literal">
       NOWAIT
      </code>
      causes a
      <code class="literal">
       FOR
          UPDATE
      </code>
      or
      <code class="literal">
       FOR SHARE
      </code>
      query to
          execute immediately, returning an error if a row lock cannot
          be obtained due to a lock held by another transaction.
     </p>
     <p>
      <code class="literal">
       SKIP LOCKED
      </code>
      causes a
      <code class="literal">
       FOR
          UPDATE
      </code>
      or
      <code class="literal">
       FOR SHARE
      </code>
      query to
          execute immediately, excluding rows from the result set that
          are locked by another transaction.
     </p>
     <p>
      <code class="literal">
       NOWAIT
      </code>
      and
      <code class="literal">
       SKIP LOCKED
      </code>
      options are unsafe for statement-based replication.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Queries that skip locked rows return an inconsistent view of
            the data.
       <code class="literal">
        SKIP LOCKED
       </code>
       is therefore not
            suitable for general transactional work. However, it may be
            used to avoid lock contention when multiple sessions access
            the same queue-like table.
      </p>
     </div>
     <p>
      <code class="literal">
       OF
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
      </code>
      applies
      <code class="literal">
       FOR UPDATE
      </code>
      and
      <code class="literal">
       FOR
          SHARE
      </code>
      queries to named tables. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa86661077"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">,</span> t2 <span class="token keyword">FOR</span> <span class="token keyword">SHARE</span> <span class="token keyword">OF</span> t1 <span class="token keyword">FOR</span> <span class="token keyword">UPDATE</span> <span class="token keyword">OF</span> t2<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      All tables referenced by the query block are locked when
      <code class="literal">
       OF
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
      </code>
      is
          omitted. Consequently, using a locking clause without
      <code class="literal">
       OF
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
      </code>
      in
          combination with another locking clause returns an error.
          Specifying the same table in multiple locking clauses returns
          an error. If an alias is specified as the table name in the
      <code class="literal">
       SELECT
      </code>
      statement, a locking clause may only
          use the alias. If the
      <code class="literal">
       SELECT
      </code>
      statement does
          not specify an alias explicitly, the locking clause may only
          specify the actual table name.
     </p>
     <p>
      For more information about
      <code class="literal">
       FOR UPDATE
      </code>
      and
      <code class="literal">
       FOR SHARE
      </code>
      , see
      <a class="xref" href="innodb-locking-reads.html" title="17.7.2.4 Locking Reads">
       Section 17.7.2.4, “Locking Reads”
      </a>
      . For additional
          information about
      <code class="literal">
       NOWAIT
      </code>
      and
      <code class="literal">
       SKIP
          LOCKED
      </code>
      options, see
      <a class="xref" href="innodb-locking-reads.html#innodb-locking-reads-nowait-skip-locked" title="Locking Read Concurrency with NOWAIT and SKIP LOCKED">
       Locking Read Concurrency with NOWAIT and SKIP LOCKED
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Following the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   keyword, you
      can use a number of modifiers that affect the operation of the
      statement.
   <code class="literal">
    HIGH_PRIORITY
   </code>
   ,
   <code class="literal">
    STRAIGHT_JOIN
   </code>
   , and modifiers beginning with
   <code class="literal">
    SQL_
   </code>
   are MySQL extensions to standard SQL.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <code class="literal">
       ALL
      </code>
      and
      <code class="literal">
       DISTINCT
      </code>
      modifiers specify whether duplicate rows should be returned.
      <code class="literal">
       ALL
      </code>
      (the default) specifies that all
          matching rows should be returned, including duplicates.
      <code class="literal">
       DISTINCT
      </code>
      specifies removal of duplicate
          rows from the result set. It is an error to specify both
          modifiers.
      <code class="literal">
       DISTINCTROW
      </code>
      is a synonym for
      <code class="literal">
       DISTINCT
      </code>
      .
     </p>
     <p>
      <code class="literal">
       DISTINCT
      </code>
      can be used with a query that also
          uses
      <code class="literal">
       WITH ROLLUP
      </code>
      .
     </p>
     <a class="indexterm" name="idm46045180760944">
     </a>
     <a class="indexterm" name="idm46045180759488">
     </a>
     <a class="indexterm" name="idm46045180758000">
     </a>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       HIGH_PRIORITY
      </code>
      gives the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      higher priority than a
          statement that updates a table. You should use this only for
          queries that are very fast and must be done at once. A
      <code class="literal">
       SELECT HIGH_PRIORITY
      </code>
      query that is issued
          while the table is locked for reading runs even if there is an
          update statement waiting for the table to be free. This
          affects only storage engines that use only table-level locking
          (such as
      <code class="literal">
       MyISAM
      </code>
      ,
      <code class="literal">
       MEMORY
      </code>
      ,
          and
      <code class="literal">
       MERGE
      </code>
      ).
     </p>
     <a class="indexterm" name="idm46045180750544">
     </a>
     <p>
      <code class="literal">
       HIGH_PRIORITY
      </code>
      cannot be used with
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements that are part
          of a
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       STRAIGHT_JOIN
      </code>
      forces the optimizer to join
          the tables in the order in which they are listed in the
      <code class="literal">
       FROM
      </code>
      clause. You can use this to speed up a
          query if the optimizer joins the tables in nonoptimal order.
      <code class="literal">
       STRAIGHT_JOIN
      </code>
      also can be used in the
      <em class="replaceable">
       <code>
        table_references
       </code>
      </em>
      list. See
      <a class="xref" href="join.html" title="15.2.13.2 JOIN Clause">
       Section 15.2.13.2, “JOIN Clause”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045180741168">
     </a>
     <a class="indexterm" name="idm46045180739680">
     </a>
     <a class="indexterm" name="idm46045180738192">
     </a>
     <p>
      <code class="literal">
       STRAIGHT_JOIN
      </code>
      does not apply to any table
          that the optimizer treats as a
      <a class="link" href="explain-output.html#jointype_const">
       <code class="literal">
        const
       </code>
      </a>
      or
      <a class="link" href="explain-output.html#jointype_system">
       <code class="literal">
        system
       </code>
      </a>
      table. Such a table
          produces a single row, is read during the optimization phase
          of query execution, and references to its columns are replaced
          with the appropriate column values before query execution
          proceeds. These tables appear first in the query plan
          displayed by
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      . See
      <a class="xref" href="using-explain.html" title="10.8.1 Optimizing Queries with EXPLAIN">
       Section 10.8.1, “Optimizing Queries with EXPLAIN”
      </a>
      . This exception may not apply
          to
      <a class="link" href="explain-output.html#jointype_const">
       <code class="literal">
        const
       </code>
      </a>
      or
      <a class="link" href="explain-output.html#jointype_system">
       <code class="literal">
        system
       </code>
      </a>
      tables that are used
          on the
      <code class="literal">
       NULL
      </code>
      -complemented side of an outer
          join (that is, the right-side table of a
      <code class="literal">
       LEFT
          JOIN
      </code>
      or the left-side table of a
      <code class="literal">
       RIGHT
          JOIN
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SQL_BIG_RESULT
      </code>
      or
      <code class="literal">
       SQL_SMALL_RESULT
      </code>
      can be used with
      <code class="literal">
       GROUP BY
      </code>
      or
      <code class="literal">
       DISTINCT
      </code>
      to
          tell the optimizer that the result set has many rows or is
          small, respectively. For
      <code class="literal">
       SQL_BIG_RESULT
      </code>
      ,
          MySQL directly uses disk-based temporary tables if they are
          created, and prefers sorting to using a temporary table with a
          key on the
      <code class="literal">
       GROUP BY
      </code>
      elements. For
      <code class="literal">
       SQL_SMALL_RESULT
      </code>
      , MySQL uses in-memory
          temporary tables to store the resulting table instead of using
          sorting. This should not normally be needed.
     </p>
     <a class="indexterm" name="idm46045180719888">
     </a>
     <a class="indexterm" name="idm46045180718400">
     </a>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SQL_BUFFER_RESULT
      </code>
      forces the result to be
          put into a temporary table. This helps MySQL free the table
          locks early and helps in cases where it takes a long time to
          send the result set to the client. This modifier can be used
          only for top-level
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements, not for subqueries or following
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION
       </code>
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045180712656">
     </a>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SQL_CALC_FOUND_ROWS
      </code>
      tells MySQL to
          calculate how many rows there would be in the result set,
          disregarding any
      <code class="literal">
       LIMIT
      </code>
      clause. The number
          of rows can then be retrieved with
      <code class="literal">
       SELECT
          FOUND_ROWS()
      </code>
      . See
      <a class="xref" href="information-functions.html" title="14.15 Information Functions">
       Section 14.15, “Information Functions”
      </a>
      .
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       The
       <code class="literal">
        SQL_CALC_FOUND_ROWS
       </code>
       query modifier
            and accompanying
       <a class="link" href="information-functions.html#function_found-rows">
        <code class="literal">
         FOUND_ROWS()
        </code>
       </a>
       function are deprecated; expect them to be removed in a
            future version of MySQL. See the description of
       <a class="link" href="information-functions.html#function_found-rows">
        <code class="literal">
         FOUND_ROWS()
        </code>
       </a>
       for information
            about an alternative strategy.
      </p>
     </div>
     <a class="indexterm" name="idm46045180703216">
     </a>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       SQL_CACHE
      </code>
      and
      <code class="literal">
       SQL_NO_CACHE
      </code>
      modifiers were used with the
          query cache prior to MySQL 8.4. The query cache
          was removed in MySQL 8.4. The
      <code class="literal">
       SQL_CACHE
      </code>
      modifier was removed as well.
      <code class="literal">
       SQL_NO_CACHE
      </code>
      is deprecated, and has no
          effect; expect it to be removed in a future MySQL release.
     </p>
     <a class="indexterm" name="idm46045180697792">
     </a>
     <a class="indexterm" name="idm46045180696304">
     </a>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
