<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="join">
      </a>
      15.2.13.2 JOIN Clause
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045180540304">
  </a>
  <a class="indexterm" name="idm46045180539232">
  </a>
  <a class="indexterm" name="idm46045180538160">
  </a>
  <a class="indexterm" name="idm46045180537088">
  </a>
  <a class="indexterm" name="idm46045180536016">
  </a>
  <a class="indexterm" name="idm46045180534944">
  </a>
  <a class="indexterm" name="idm46045180533872">
  </a>
  <a class="indexterm" name="idm46045180532800">
  </a>
  <a class="indexterm" name="idm46045180531728">
  </a>
  <a class="indexterm" name="idm46045180530656">
  </a>
  <a class="indexterm" name="idm46045180529584">
  </a>
  <a class="indexterm" name="idm46045180528512">
  </a>
  <a class="indexterm" name="idm46045180527440">
  </a>
  <a class="indexterm" name="idm46045180526352">
  </a>
  <a class="indexterm" name="idm46045180525280">
  </a>
  <p>
   MySQL supports the following
   <code class="literal">
    JOIN
   </code>
   syntax for
        the
   <em class="replaceable">
    <code>
     table_references
    </code>
   </em>
   part of
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements and
        multiple-table
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   and
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa813535"><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">table_references:</em>
    <em class="replaceable">escaped_table_reference</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">escaped_table_reference</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">escaped_table_reference</em>: {
    <em class="replaceable">table_reference</em>
  <span class="token operator">|</span> { <span class="token keyword">OJ</span> <em class="replaceable">table_reference</em> }
}

<em class="replaceable">table_reference</em>: {
    <em class="replaceable">table_factor</em>
  <span class="token operator">|</span> <em class="replaceable">joined_table</em>
}

<em class="replaceable">table_factor</em>: {
    <em class="replaceable">tbl_name</em> <span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_names</em><span class="token punctuation">)</span><span class="token punctuation">]</span>
        <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_list</em><span class="token punctuation">]</span>
  <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">LATERAL</span><span class="token punctuation">]</span> <em class="replaceable">table_subquery</em> <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">col_list</em><span class="token punctuation">)</span><span class="token punctuation">]</span>
  <span class="token operator">|</span> <span class="token punctuation">(</span> <em class="replaceable">table_references</em> <span class="token punctuation">)</span>
}

<em class="replaceable">joined_table</em>: {
    <em class="replaceable">table_reference</em> {<span class="token punctuation">[</span><span class="token keyword">INNER</span> <span class="token operator">|</span> <span class="token keyword">CROSS</span><span class="token punctuation">]</span> <span class="token keyword">JOIN</span> <span class="token operator">|</span> <span class="token keyword">STRAIGHT_JOIN</span>} <em class="replaceable">table_factor</em> <span class="token punctuation">[</span><em class="replaceable">join_specification</em><span class="token punctuation">]</span>
  <span class="token operator">|</span> <em class="replaceable">table_reference</em> {<span class="token keyword">LEFT</span><span class="token operator">|</span><span class="token keyword">RIGHT</span>} <span class="token punctuation">[</span><span class="token keyword">OUTER</span><span class="token punctuation">]</span> <span class="token keyword">JOIN</span> <em class="replaceable">table_reference</em> <em class="replaceable">join_specification</em>
  <span class="token operator">|</span> <em class="replaceable">table_reference</em> <span class="token keyword">NATURAL</span> <span class="token punctuation">[</span><span class="token keyword">INNER</span> <span class="token operator">|</span> {<span class="token keyword">LEFT</span><span class="token operator">|</span><span class="token keyword">RIGHT</span>} <span class="token punctuation">[</span><span class="token keyword">OUTER</span><span class="token punctuation">]</span><span class="token punctuation">]</span> <span class="token keyword">JOIN</span> <em class="replaceable">table_factor</em>
}

<em class="replaceable">join_specification</em>: {
    <span class="token keyword">ON</span> <em class="replaceable">search_condition</em>
  <span class="token operator">|</span> <span class="token keyword">USING</span> <span class="token punctuation">(</span><em class="replaceable">join_column_list</em><span class="token punctuation">)</span>
}

<em class="replaceable">join_column_list</em>:
    <span class="token keyword"><em class="replaceable">column_name</em></span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword"><em class="replaceable">column_name</em></span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">index_hint_list</em>:
    <em class="replaceable">index_hint</em><span class="token punctuation">[</span> <em class="replaceable">index_hint</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">index_hint</em>: {
    <span class="token keyword">USE</span> {<span class="token keyword">INDEX</span><span class="token operator">|</span><span class="token keyword">KEY</span>}
      <span class="token punctuation">[</span><span class="token keyword">FOR</span> {<span class="token keyword">JOIN</span><span class="token operator">|</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span><span class="token operator">|</span><span class="token keyword">GROUP</span> <span class="token keyword">BY</span>}<span class="token punctuation">]</span> <span class="token punctuation">(</span><span class="token punctuation">[</span><em class="replaceable">index_list</em><span class="token punctuation">]</span><span class="token punctuation">)</span>
  <span class="token operator">|</span> {<span class="token keyword">IGNORE</span><span class="token operator">|</span><span class="token keyword">FORCE</span>} {<span class="token keyword">INDEX</span><span class="token operator">|</span><span class="token keyword">KEY</span>}
      <span class="token punctuation">[</span><span class="token keyword">FOR</span> {<span class="token keyword">JOIN</span><span class="token operator">|</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span><span class="token operator">|</span><span class="token keyword">GROUP</span> <span class="token keyword">BY</span>}<span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">index_list</em><span class="token punctuation">)</span>
}

<em class="replaceable">index_list</em>:
    <em class="replaceable">index_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">index_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 table reference is also known as a join expression.
  </p>
  <p>
   A table reference (when it refers to a partitioned table) may
        contain a
   <code class="literal">
    PARTITION
   </code>
   clause, including a list
        of comma-separated partitions, subpartitions, or both. This
        option follows the name of the table and precedes any alias
        declaration. The effect of this option is that rows are selected
        only from the listed partitions or subpartitions. Any partitions
        or subpartitions not named in the list 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>
  <p>
   The syntax of
   <em class="replaceable">
    <code>
     table_factor
    </code>
   </em>
   is
        extended in MySQL in comparison with standard SQL. The standard
        accepts only
   <em class="replaceable">
    <code>
     table_reference
    </code>
   </em>
   , not a
        list of them inside a pair of parentheses.
  </p>
  <p>
   This is a conservative extension if each comma in a list of
   <em class="replaceable">
    <code>
     table_reference
    </code>
   </em>
   items is considered
        as equivalent to an inner join. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa62044094"><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">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>t2<span class="token punctuation">,</span> t3<span class="token punctuation">,</span> t4<span class="token punctuation">)</span>
                 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>a <span class="token operator">=</span> t1<span class="token punctuation">.</span>a <span class="token operator">AND</span> t3<span class="token punctuation">.</span>b <span class="token operator">=</span> t1<span class="token punctuation">.</span>b <span class="token operator">AND</span> t4<span class="token punctuation">.</span>c <span class="token operator">=</span> t1<span class="token punctuation">.</span>c<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   is equivalent to:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87079123"><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">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>t2 <span class="token keyword">CROSS</span> <span class="token keyword">JOIN</span> t3 <span class="token keyword">CROSS</span> <span class="token keyword">JOIN</span> t4<span class="token punctuation">)</span>
                 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>a <span class="token operator">=</span> t1<span class="token punctuation">.</span>a <span class="token operator">AND</span> t3<span class="token punctuation">.</span>b <span class="token operator">=</span> t1<span class="token punctuation">.</span>b <span class="token operator">AND</span> t4<span class="token punctuation">.</span>c <span class="token operator">=</span> t1<span class="token punctuation">.</span>c<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   In MySQL,
   <code class="literal">
    JOIN
   </code>
   ,
   <code class="literal">
    CROSS
        JOIN
   </code>
   , and
   <code class="literal">
    INNER JOIN
   </code>
   are syntactic
        equivalents (they can replace each other). In standard SQL, they
        are not equivalent.
   <code class="literal">
    INNER JOIN
   </code>
   is used with
        an
   <code class="literal">
    ON
   </code>
   clause,
   <code class="literal">
    CROSS JOIN
   </code>
   is used otherwise.
  </p>
  <p>
   In general, parentheses can be ignored in join expressions
        containing only inner join operations. MySQL also supports
        nested joins. See
   <a class="xref" href="nested-join-optimization.html" title="10.2.1.8 Nested Join Optimization">
    Section 10.2.1.8, “Nested Join Optimization”
   </a>
   .
  </p>
  <p>
   Index hints can be specified to affect how the MySQL optimizer
        makes use of indexes. For more information, see
   <a class="xref" href="index-hints.html" title="10.9.4 Index Hints">
    Section 10.9.4, “Index Hints”
   </a>
   . Optimizer hints and the
   <code class="literal">
    optimizer_switch
   </code>
   system variable are other
        ways to influence optimizer use of indexes. See
   <a class="xref" href="optimizer-hints.html" title="10.9.3 Optimizer Hints">
    Section 10.9.3, “Optimizer Hints”
   </a>
   , and
   <a class="xref" href="switchable-optimizations.html" title="10.9.2 Switchable Optimizations">
    Section 10.9.2, “Switchable Optimizations”
   </a>
   .
  </p>
  <p>
   The following list describes general factors to take into
        account when writing joins:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      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>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3373174"><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 keyword">INNER</span> <span class="token keyword">JOIN</span> info <span class="token keyword">AS</span> t2 <span class="token keyword">ON</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 keyword">INNER</span> <span class="token keyword">JOIN</span> info t2 <span class="token keyword">ON</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
      <em class="replaceable">
       <code>
        table_subquery
       </code>
      </em>
      is also known as
            a derived table or subquery in the
      <code class="literal">
       FROM
      </code>
      clause. See
      <a class="xref" href="derived-tables.html" title="15.2.15.8 Derived Tables">
       Section 15.2.15.8, “Derived Tables”
      </a>
      . Such
            subqueries
      <span class="emphasis">
       <em>
        must
       </em>
      </span>
      include an alias to
            give the subquery result a table name, and may optionally
            include a list of table column names in parentheses. A
            trivial example follows:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa25882108"><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> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> t1<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The maximum number of tables that can be referenced in a
            single join is 61. This includes a join handled by merging
            derived tables and views in the
      <code class="literal">
       FROM
      </code>
      clause into the outer query block (see
      <a class="xref" href="derived-table-optimization.html" title="10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization">
       Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions
          with Merging or Materialization”
      </a>
      ).
     </p>
     <a class="indexterm" name="idm46045180468256">
     </a>
     <a class="indexterm" name="idm46045180466768">
     </a>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INNER JOIN
      </code>
      and
      <code class="literal">
       ,
      </code>
      (comma) are semantically equivalent in the absence of a join
            condition: both produce a Cartesian product between the
            specified tables (that is, each and every row in the first
            table is joined to each and every row in the second table).
     </p>
     <p>
      However, the precedence of the comma operator is less than
            that of
      <code class="literal">
       INNER JOIN
      </code>
      ,
      <code class="literal">
       CROSS
            JOIN
      </code>
      ,
      <code class="literal">
       LEFT JOIN
      </code>
      , and so on. If
            you mix comma joins with the other join types when there is
            a join condition, an error of the form
      <code class="literal">
       Unknown
            column '
       <em class="replaceable">
        <code>
         col_name
        </code>
       </em>
       ' in 'on
            clause'
      </code>
      may occur. Information about dealing with
            this problem is given later in this section.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <em class="replaceable">
       <code>
        search_condition
       </code>
      </em>
      used with
      <code class="literal">
       ON
      </code>
      is any conditional expression of the
            form that can be used in a
      <code class="literal">
       WHERE
      </code>
      clause.
            Generally, the
      <code class="literal">
       ON
      </code>
      clause serves for
            conditions that specify how to join tables, and the
      <code class="literal">
       WHERE
      </code>
      clause restricts which rows to
            include in the result set.
     </p>
    </li>
    <li class="listitem">
     <p>
      If there is no matching row for the right table in the
      <code class="literal">
       ON
      </code>
      or
      <code class="literal">
       USING
      </code>
      part in a
      <code class="literal">
       LEFT JOIN
      </code>
      , a row with all columns set to
      <code class="literal">
       NULL
      </code>
      is used for the right table. You can
            use this fact to find rows in a table that have no
            counterpart in another table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28834963"><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> left_tbl<span class="token punctuation">.</span><span class="token operator">*</span>
  <span class="token keyword">FROM</span> left_tbl <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> right_tbl <span class="token keyword">ON</span> left_tbl<span class="token punctuation">.</span>id <span class="token operator">=</span> right_tbl<span class="token punctuation">.</span>id
  <span class="token keyword">WHERE</span> right_tbl<span class="token punctuation">.</span>id <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This example finds all rows in
      <code class="literal">
       left_tbl
      </code>
      with an
      <code class="literal">
       id
      </code>
      value that is not present in
      <code class="literal">
       right_tbl
      </code>
      (that is, all rows in
      <code class="literal">
       left_tbl
      </code>
      with no corresponding row in
      <code class="literal">
       right_tbl
      </code>
      ). See
      <a class="xref" href="outer-join-optimization.html" title="10.2.1.9 Outer Join Optimization">
       Section 10.2.1.9, “Outer Join Optimization”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       USING(
       <em class="replaceable">
        <code>
         join_column_list
        </code>
       </em>
       )
      </code>
      clause names a list of columns that must exist in both
            tables. If tables
      <code class="literal">
       a
      </code>
      and
      <code class="literal">
       b
      </code>
      both contain columns
      <code class="literal">
       c1
      </code>
      ,
      <code class="literal">
       c2
      </code>
      , and
      <code class="literal">
       c3
      </code>
      , the following join compares
            corresponding columns from the two tables:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa15026346"><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">a <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> b <span class="token keyword">USING</span> <span class="token punctuation">(</span>c1<span class="token punctuation">,</span> c2<span class="token punctuation">,</span> c3<span class="token punctuation">)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       NATURAL [LEFT] JOIN
      </code>
      of two tables is
            defined to be semantically equivalent to an
      <code class="literal">
       INNER
            JOIN
      </code>
      or a
      <code class="literal">
       LEFT JOIN
      </code>
      with a
      <code class="literal">
       USING
      </code>
      clause that names all columns that
            exist in both tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       RIGHT JOIN
      </code>
      works analogously to
      <code class="literal">
       LEFT JOIN
      </code>
      . To keep code portable across
            databases, it is recommended that you use
      <code class="literal">
       LEFT
            JOIN
      </code>
      instead of
      <code class="literal">
       RIGHT JOIN
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045180429712">
      </a>
      <a class="indexterm" name="idm46045180428640">
      </a>
      The
      <code class="literal">
       { OJ ... }
      </code>
      syntax shown in the join
            syntax description exists only for compatibility with ODBC.
            The curly braces in the syntax should be written literally;
            they are not metasyntax as used elsewhere in syntax
            descriptions.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa85504815"><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> left_tbl<span class="token punctuation">.</span><span class="token operator">*</span>
    <span class="token keyword">FROM</span> { <span class="token keyword">OJ</span> left_tbl <span class="token keyword">LEFT</span> <span class="token keyword">OUTER</span> <span class="token keyword">JOIN</span> right_tbl
           <span class="token keyword">ON</span> left_tbl<span class="token punctuation">.</span>id <span class="token operator">=</span> right_tbl<span class="token punctuation">.</span>id }
    <span class="token keyword">WHERE</span> right_tbl<span class="token punctuation">.</span>id <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      You can use other types of joins within
      <code class="literal">
       { OJ ...
            }
      </code>
      , such as
      <code class="literal">
       INNER JOIN
      </code>
      or
      <code class="literal">
       RIGHT OUTER JOIN
      </code>
      . This helps with
            compatibility with some third-party applications, but is not
            official ODBC syntax.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       STRAIGHT_JOIN
      </code>
      is similar to
      <code class="literal">
       JOIN
      </code>
      , except that the left table is
            always read before the right table. This can be used for
            those (few) cases for which the join optimizer processes the
            tables in a suboptimal order.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Some join examples:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28887376"><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> table1<span class="token punctuation">,</span> table2<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> table1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> table2 <span class="token keyword">ON</span> table1<span class="token punctuation">.</span>id <span class="token operator">=</span> table2<span class="token punctuation">.</span>id<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> table1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> table2 <span class="token keyword">ON</span> table1<span class="token punctuation">.</span>id <span class="token operator">=</span> table2<span class="token punctuation">.</span>id<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> table1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> table2 <span class="token keyword">USING</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> table1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> table2 <span class="token keyword">ON</span> table1<span class="token punctuation">.</span>id <span class="token operator">=</span> table2<span class="token punctuation">.</span>id
  <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> table3 <span class="token keyword">ON</span> table2<span class="token punctuation">.</span>id <span class="token operator">=</span> table3<span class="token punctuation">.</span>id<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Natural joins and joins with
   <code class="literal">
    USING
   </code>
   , including
        outer join variants, are processed according to the SQL:2003
        standard:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Redundant columns of a
      <code class="literal">
       NATURAL
      </code>
      join do
            not appear. Consider this set of statements:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7254345"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">,</span> j <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>k <span class="token datatype">INT</span><span class="token punctuation">,</span> j <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">NATURAL</span> <span class="token keyword">JOIN</span> t2<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">JOIN</span> t2 <span class="token keyword">USING</span> <span class="token punctuation">(</span>j<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      In the first
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement, column
      <code class="literal">
       j
      </code>
      appears in both
            tables and thus becomes a join column, so, according to
            standard SQL, it should appear only once in the output, not
            twice. Similarly, in the second SELECT statement, column
      <code class="literal">
       j
      </code>
      is named in the
      <code class="literal">
       USING
      </code>
      clause and should appear only once
            in the output, not twice.
     </p>
     <p>
      Thus, the statements produce this output:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa5032925"><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-none">+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+</code></pre>
     </div>
     <p>
      Redundant column elimination and column ordering occurs
            according to standard SQL, producing this display order:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         First, coalesced common columns of the two joined
                tables, in the order in which they occur in the first
                table
        </p>
       </li>
       <li class="listitem">
        <p>
         Second, columns unique to the first table, in order in
                which they occur in that table
        </p>
       </li>
       <li class="listitem">
        <p>
         Third, columns unique to the second table, in order in
                which they occur in that table
        </p>
       </li>
      </ul>
     </div>
     <p>
      The single result column that replaces two common columns is
            defined using the coalesce operation. That is, for two
      <code class="literal">
       t1.a
      </code>
      and
      <code class="literal">
       t2.a
      </code>
      the
            resulting single join column
      <code class="literal">
       a
      </code>
      is defined
            as
      <code class="literal">
       a = COALESCE(t1.a, t2.a)
      </code>
      , where:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa28147654"><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 function">COALESCE</span><span class="token punctuation">(</span>x<span class="token punctuation">,</span> y<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token operator">CASE</span> <span class="token keyword">WHEN</span> x <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">THEN</span> x <span class="token keyword">ELSE</span> y <span class="token keyword">END</span><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      If the join operation is any other join, the result columns
            of the join consist of the concatenation of all columns of
            the joined tables.
     </p>
     <p>
      A consequence of the definition of coalesced columns is
            that, for outer joins, the coalesced column contains the
            value of the non-
      <code class="literal">
       NULL
      </code>
      column if one of
            the two columns is always
      <code class="literal">
       NULL
      </code>
      . If
            neither or both columns are
      <code class="literal">
       NULL
      </code>
      , both
            common columns have the same value, so it doesn't matter
            which one is chosen as the value of the coalesced column. A
            simple way to interpret this is to consider that a coalesced
            column of an outer join is represented by the common column
            of the inner table of a
      <code class="literal">
       JOIN
      </code>
      . Suppose
            that the tables
      <code class="literal">
       t1(a, b)
      </code>
      and
      <code class="literal">
       t2(a, c)
      </code>
      have the following contents:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa68687844"><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-none">t1    t2
----  ----
1 x   2 z
2 y   3 w</code></pre>
     </div>
     <p>
      Then, for this join, column
      <code class="literal">
       a
      </code>
      contains
            the values of
      <code class="literal">
       t1.a
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93774344"><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 operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">NATURAL</span> <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t2<span class="token punctuation">;</span>
<span class="token output"><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 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 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 punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span> c    <span class="token punctuation">|</span></span>
<span class="token output"><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 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 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 punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span> x    <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> y    <span class="token punctuation">|</span> z    <span class="token punctuation">|</span></span>
<span class="token output"><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 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 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 punctuation">+</span></span></code></pre>
     </div>
     <p>
      By contrast, for this join, column
      <code class="literal">
       a
      </code>
      contains the values of
      <code class="literal">
       t2.a
      </code>
      .
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66635764"><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 operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">NATURAL</span> <span class="token keyword">RIGHT</span> <span class="token keyword">JOIN</span> t2<span class="token punctuation">;</span>
<span class="token output"><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 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 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 punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> c    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span></span>
<span class="token output"><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 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 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 punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> z    <span class="token punctuation">|</span> y    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span> w    <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span></span>
<span class="token output"><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 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 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 punctuation">+</span></span></code></pre>
     </div>
     <p>
      Compare those results to the otherwise equivalent queries
            with
      <code class="literal">
       JOIN ... ON
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33012725"><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 operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>a <span class="token operator">=</span> t2<span class="token punctuation">.</span>a<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><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 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 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 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 punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span> a    <span class="token punctuation">|</span> c    <span class="token punctuation">|</span></span>
<span class="token output"><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 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 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 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 punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span> x    <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> y    <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> z    <span class="token punctuation">|</span></span>
<span class="token output"><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 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 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 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 punctuation">+</span></span></code></pre>
     </div>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58802097"><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 operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">RIGHT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>a <span class="token operator">=</span> t2<span class="token punctuation">.</span>a<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><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 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 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 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 punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span> a    <span class="token punctuation">|</span> c    <span class="token punctuation">|</span></span>
<span class="token output"><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 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 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 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 punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> y    <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> z    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>    3 <span class="token punctuation">|</span> w    <span class="token punctuation">|</span></span>
<span class="token output"><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 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 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 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 punctuation">+</span></span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      A
      <code class="literal">
       USING
      </code>
      clause can be rewritten as an
      <code class="literal">
       ON
      </code>
      clause that compares corresponding
            columns. However, although
      <code class="literal">
       USING
      </code>
      and
      <code class="literal">
       ON
      </code>
      are similar, they are not quite the
            same. Consider the following two queries:
     </p>
     <a class="indexterm" name="idm46045180377008">
     </a>
     <a class="indexterm" name="idm46045180375520">
     </a>
     <a class="indexterm" name="idm46045180374032">
     </a>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44783911"><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">a <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> b <span class="token keyword">USING</span> <span class="token punctuation">(</span>c1<span class="token punctuation">,</span> c2<span class="token punctuation">,</span> c3<span class="token punctuation">)</span>
a <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> b <span class="token keyword">ON</span> a<span class="token punctuation">.</span>c1 <span class="token operator">=</span> b<span class="token punctuation">.</span>c1 <span class="token operator">AND</span> a<span class="token punctuation">.</span>c2 <span class="token operator">=</span> b<span class="token punctuation">.</span>c2 <span class="token operator">AND</span> a<span class="token punctuation">.</span>c3 <span class="token operator">=</span> b<span class="token punctuation">.</span>c3</code></pre>
     </div>
     <p>
      With respect to determining which rows satisfy the join
            condition, both joins are semantically identical.
     </p>
     <p>
      With respect to determining which columns to display for
      <code class="literal">
       SELECT *
      </code>
      expansion, the two joins are not
            semantically identical. The
      <code class="literal">
       USING
      </code>
      join
            selects the coalesced value of corresponding columns,
            whereas the
      <code class="literal">
       ON
      </code>
      join selects all columns
            from all tables. For the
      <code class="literal">
       USING
      </code>
      join,
      <code class="literal">
       SELECT *
      </code>
      selects these values:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47689172"><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 function">COALESCE</span><span class="token punctuation">(</span>a<span class="token punctuation">.</span>c1<span class="token punctuation">,</span> b<span class="token punctuation">.</span>c1<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">COALESCE</span><span class="token punctuation">(</span>a<span class="token punctuation">.</span>c2<span class="token punctuation">,</span> b<span class="token punctuation">.</span>c2<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">COALESCE</span><span class="token punctuation">(</span>a<span class="token punctuation">.</span>c3<span class="token punctuation">,</span> b<span class="token punctuation">.</span>c3<span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      For the
      <code class="literal">
       ON
      </code>
      join,
      <code class="literal">
       SELECT
            *
      </code>
      selects these values:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa27552502"><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-none">a.c1, a.c2, a.c3, b.c1, b.c2, b.c3</code></pre>
     </div>
     <p>
      With an inner join,
      <a class="link" href="comparison-operators.html#function_coalesce">
       <code class="literal">
        COALESCE(a.c1,
            b.c1)
       </code>
      </a>
      is the same as either
      <code class="literal">
       a.c1
      </code>
      or
      <code class="literal">
       b.c1
      </code>
      because
            both columns have the same value. With an outer join (such
            as
      <code class="literal">
       LEFT JOIN
      </code>
      ), one of the two columns can
            be
      <code class="literal">
       NULL
      </code>
      . That column is omitted from the
            result.
     </p>
    </li>
    <li class="listitem">
     <p>
      An
      <code class="literal">
       ON
      </code>
      clause can refer only to its
            operands.
     </p>
     <p>
      Example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79013205"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>i1 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>i2 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t3 <span class="token punctuation">(</span>i3 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> <span class="token punctuation">(</span>i1 <span class="token operator">=</span> i3<span class="token punctuation">)</span> <span class="token keyword">JOIN</span> t3<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The statement fails with an
      <code class="literal">
       Unknown column 'i3' in
            'on clause'
      </code>
      error because
      <code class="literal">
       i3
      </code>
      is
            a column in
      <code class="literal">
       t3
      </code>
      , which is not an operand
            of the
      <code class="literal">
       ON
      </code>
      clause. To enable the join to
            be processed, rewrite the statement as follows:
     </p>
     <a class="indexterm" name="idm46045180351760">
     </a>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa43174518"><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">JOIN</span> t2 <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> <span class="token punctuation">(</span>i1 <span class="token operator">=</span> i3<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       JOIN
      </code>
      has higher precedence than the comma
            operator (
      <code class="literal">
       ,
      </code>
      ), so the join expression
      <code class="literal">
       t1, t2 JOIN t3
      </code>
      is interpreted as
      <code class="literal">
       (t1, (t2 JOIN t3))
      </code>
      , not as
      <code class="literal">
       ((t1,
            t2) JOIN t3)
      </code>
      . This affects statements that use an
      <code class="literal">
       ON
      </code>
      clause because that clause can refer
            only to columns in the operands of the join, and the
            precedence affects interpretation of what those operands
            are.
     </p>
     <p>
      Example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22209856"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>i1 <span class="token datatype">INT</span><span class="token punctuation">,</span> j1 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>i2 <span class="token datatype">INT</span><span class="token punctuation">,</span> j2 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t3 <span class="token punctuation">(</span>i3 <span class="token datatype">INT</span><span class="token punctuation">,</span> j3 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t3 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<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">JOIN</span> t3 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>i1 <span class="token operator">=</span> t3<span class="token punctuation">.</span>i3<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The
      <code class="literal">
       JOIN
      </code>
      takes precedence over the comma
            operator, so the operands for the
      <code class="literal">
       ON
      </code>
      clause are
      <code class="literal">
       t2
      </code>
      and
      <code class="literal">
       t3
      </code>
      .
            Because
      <code class="literal">
       t1.i1
      </code>
      is not a column in either
            of the operands, the result is an
      <code class="literal">
       Unknown column
            't1.i1' in 'on clause'
      </code>
      error.
     </p>
     <a class="indexterm" name="idm46045180337984">
     </a>
     <p>
      To enable the join to be processed, use either of these
            strategies:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Group the first two tables explicitly with parentheses
                so that the operands for the
         <code class="literal">
          ON
         </code>
         clause are
         <code class="literal">
          (t1, t2)
         </code>
         and
         <code class="literal">
          t3
         </code>
         :
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa2330367"><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> <span class="token punctuation">(</span>t1<span class="token punctuation">,</span> t2<span class="token punctuation">)</span> <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>i1 <span class="token operator">=</span> t3<span class="token punctuation">.</span>i3<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
        </div>
       </li>
       <li class="listitem">
        <p>
         Avoid the use of the comma operator and use
         <code class="literal">
          JOIN
         </code>
         instead:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa54307684"><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">JOIN</span> t2 <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>i1 <span class="token operator">=</span> t3<span class="token punctuation">.</span>i3<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
        </div>
       </li>
      </ul>
     </div>
     <p>
      The same precedence interpretation also applies to
            statements that mix the comma operator with
      <code class="literal">
       INNER
            JOIN
      </code>
      ,
      <code class="literal">
       CROSS JOIN
      </code>
      ,
      <code class="literal">
       LEFT
            JOIN
      </code>
      , and
      <code class="literal">
       RIGHT JOIN
      </code>
      , all of
            which have higher precedence than the comma operator.
     </p>
    </li>
    <li class="listitem">
     <p>
      A MySQL extension compared to the SQL:2003 standard is that
            MySQL permits you to qualify the common (coalesced) columns
            of
      <code class="literal">
       NATURAL
      </code>
      or
      <code class="literal">
       USING
      </code>
      joins, whereas the standard disallows that.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
