<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="identifier-qualifiers">
      </a>
      11.2.2 Identifier Qualifiers
     </h3>
    </div>
   </div>
  </div>
  <p>
   Object names may be unqualified or qualified. An unqualified
        name is permitted in contexts where interpretation of the name
        is unambiguous. A qualified name includes at least one qualifier
        to clarify the interpretive context by overriding a default
        context or providing missing context.
  </p>
  <p>
   For example, this statement creates a table using the
        unqualified name
   <code class="literal">
    t1
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa65330541"><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><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Because
   <code class="literal">
    t1
   </code>
   includes no qualifier to specify a
        database, the statement creates the table in the default
        database. If there is no default database, an error occurs.
  </p>
  <p>
   This statement creates a table using the qualified name
   <code class="literal">
    db1.t1
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa15696632"><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> db1<span class="token punctuation">.</span>t1 <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Because
   <code class="literal">
    db1.t1
   </code>
   includes a database qualifier
   <code class="literal">
    db1
   </code>
   , the statement creates
   <code class="literal">
    t1
   </code>
   in the database named
   <code class="literal">
    db1
   </code>
   , regardless of the default database. The
        qualifier
   <span class="emphasis">
    <em>
     must
    </em>
   </span>
   be specified if there is no
        default database. The qualifier
   <span class="emphasis">
    <em>
     may
    </em>
   </span>
   be
        specified if there is a default database, to specify a database
        different from the default, or to make the database explicit if
        the default is the same as the one specified.
  </p>
  <p>
   Qualifiers have these characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      An unqualified name consists of a single identifier. A
            qualified name consists of multiple identifiers.
     </p>
    </li>
    <li class="listitem">
     <p>
      The components of a multiple-part name must be separated by
            period (
      <code class="literal">
       .
      </code>
      ) characters. The initial parts
            of a multiple-part name act as qualifiers that affect the
            context within which to interpret the final identifier.
     </p>
    </li>
    <li class="listitem">
     <p>
      The qualifier character is a separate token and need not be
            contiguous with the associated identifiers. For example,
      <em class="replaceable">
       <code>
        tbl_name.col_name
       </code>
      </em>
      and
      <em class="replaceable">
       <code>
        tbl_name . col_name
       </code>
      </em>
      are
            equivalent.
     </p>
    </li>
    <li class="listitem">
     <p>
      If any components of a multiple-part name require quoting,
            quote them individually rather than quoting the name as a
            whole. For example, write
      <code class="literal">
       `my-table`.`my-column`
      </code>
      , not
      <code class="literal">
       `my-table.my-column`
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      A reserved word that follows a period in a qualified name
            must be an identifier, so in that context it need not be
            quoted.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The permitted qualifiers for object names depend on the object
        type:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A database name is fully qualified and takes no qualifier:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa16482361"><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">DATABASE</span> db1<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      A table, view, or stored program name may be given a
            database-name qualifier. Examples of unqualified and
            qualified names in
      <code class="literal">
       CREATE
      </code>
      statements:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90218257"><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> mytable <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> myview <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> myproc <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> myfunc <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">EVENT</span> myevent <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> mydb<span class="token punctuation">.</span>mytable <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> mydb<span class="token punctuation">.</span>myview <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> mydb<span class="token punctuation">.</span>myproc <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> mydb<span class="token punctuation">.</span>myfunc <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">EVENT</span> mydb<span class="token punctuation">.</span>myevent <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      A trigger is associated with a table, so any qualifier
            applies to the table name:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa245474"><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">TRIGGER</span> mytrigger <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">ON</span> mytable <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> mytrigger <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">ON</span> mydb<span class="token punctuation">.</span>mytable <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      A column name may be given multiple qualifiers to indicate
            context in statements that reference it, as shown in the
            following table.
     </p>
     <div class="informaltable">
      <table summary="Column reference formats that can be used to refer to table columns.">
       <colgroup>
        <col style="width: 35%"/>
        <col style="width: 65%"/>
       </colgroup>
       <thead>
        <tr>
         <th>
          Column Reference
         </th>
         <th>
          Meaning
         </th>
        </tr>
       </thead>
       <tbody>
        <tr>
         <td>
          <em class="replaceable">
           <code>
            col_name
           </code>
          </em>
         </td>
         <td>
          Column
          <em class="replaceable">
           <code>
            col_name
           </code>
          </em>
          from whichever table used in
                  the statement contains a column of that name
         </td>
        </tr>
        <tr>
         <td>
          <em class="replaceable">
           <code>
            tbl_name.col_name
           </code>
          </em>
         </td>
         <td>
          Column
          <em class="replaceable">
           <code>
            col_name
           </code>
          </em>
          from table
          <em class="replaceable">
           <code>
            tbl_name
           </code>
          </em>
          of the default
                  database
         </td>
        </tr>
        <tr>
         <td>
          <em class="replaceable">
           <code>
            db_name.tbl_name.col_name
           </code>
          </em>
         </td>
         <td>
          Column
          <em class="replaceable">
           <code>
            col_name
           </code>
          </em>
          from table
          <em class="replaceable">
           <code>
            tbl_name
           </code>
          </em>
          of the database
          <em class="replaceable">
           <code>
            db_name
           </code>
          </em>
         </td>
        </tr>
       </tbody>
      </table>
     </div>
     <p>
      In other words, a column name may be given a table-name
            qualifier, which itself may be given a database-name
            qualifier. Examples of unqualified and qualified column
            references in
      <code class="literal">
       SELECT
      </code>
      statements:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa39650435"><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> c1 <span class="token keyword">FROM</span> mytable
<span class="token keyword">WHERE</span> c2 <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> mytable<span class="token punctuation">.</span>c1 <span class="token keyword">FROM</span> mytable
<span class="token keyword">WHERE</span> mytable<span class="token punctuation">.</span>c2 <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> mydb<span class="token punctuation">.</span>mytable<span class="token punctuation">.</span>c1 <span class="token keyword">FROM</span> mydb<span class="token punctuation">.</span>mytable
<span class="token keyword">WHERE</span> mydb<span class="token punctuation">.</span>mytable<span class="token punctuation">.</span>c2 <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   You need not specify a qualifier for an object reference in a
        statement unless the unqualified reference is ambiguous. Suppose
        that column
   <code class="literal">
    c1
   </code>
   occurs only in table
   <code class="literal">
    t1
   </code>
   ,
   <code class="literal">
    c2
   </code>
   only in
   <code class="literal">
    t2
   </code>
   , and
   <code class="literal">
    c
   </code>
   in both
   <code class="literal">
    t1
   </code>
   and
   <code class="literal">
    t2
   </code>
   . Any unqualified
        reference to
   <code class="literal">
    c
   </code>
   is ambiguous in a statement
        that refers to both tables and must be qualified as
   <code class="literal">
    t1.c
   </code>
   or
   <code class="literal">
    t2.c
   </code>
   to indicate
        which table you mean:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa85257659"><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> c1<span class="token punctuation">,</span> c2<span class="token punctuation">,</span> t1<span class="token punctuation">.</span>c <span class="token keyword">FROM</span> t1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> t2
<span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>c <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Similarly, to retrieve from a table
   <code class="literal">
    t
   </code>
   in
        database
   <code class="literal">
    db1
   </code>
   and from a table
   <code class="literal">
    t
   </code>
   in database
   <code class="literal">
    db2
   </code>
   in the
        same statement, you must qualify the table references: For
        references to columns in those tables, qualifiers are required
        only for column names that appear in both tables. Suppose that
        column
   <code class="literal">
    c1
   </code>
   occurs only in table
   <code class="literal">
    db1.t
   </code>
   ,
   <code class="literal">
    c2
   </code>
   only in
   <code class="literal">
    db2.t
   </code>
   , and
   <code class="literal">
    c
   </code>
   in both
   <code class="literal">
    db1.t
   </code>
   and
   <code class="literal">
    db2.t
   </code>
   . In this
        case,
   <code class="literal">
    c
   </code>
   is ambiguous and must be qualified
        but
   <code class="literal">
    c1
   </code>
   and
   <code class="literal">
    c2
   </code>
   need not be:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48686383"><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> c1<span class="token punctuation">,</span> c2<span class="token punctuation">,</span> db1<span class="token punctuation">.</span>t<span class="token punctuation">.</span>c <span class="token keyword">FROM</span> db1<span class="token punctuation">.</span>t <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> db2<span class="token punctuation">.</span>t
<span class="token keyword">WHERE</span> db2<span class="token punctuation">.</span>t<span class="token punctuation">.</span>c <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Table aliases enable qualified column references to be written
        more simply:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51250567"><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> c1<span class="token punctuation">,</span> c2<span class="token punctuation">,</span> t1<span class="token punctuation">.</span>c <span class="token keyword">FROM</span> db1<span class="token punctuation">.</span>t <span class="token keyword">AS</span> t1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> db2<span class="token punctuation">.</span>t <span class="token keyword">AS</span> t2
<span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>c <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
