<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="group-by-functional-dependence">
      </a>
      14.19.4 Detection of Functional Dependence
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045192451712">
  </a>
  <p>
   The following discussion provides several examples of the ways
        in which MySQL detects functional dependencies. The examples use
        this notation:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa5064259"><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">X</em>} <span class="token operator">-</span><span class="token operator">&gt;</span> {<em class="replaceable">Y</em>}</code></pre>
  </div>
  <p>
   Understand this as
   <span class="quote">
    “
    <span class="quote">
     <em class="replaceable">
      <code>
       X
      </code>
     </em>
     uniquely
        determines
     <em class="replaceable">
      <code>
       Y
      </code>
     </em>
     ,
    </span>
    ”
   </span>
   which also
        means that
   <em class="replaceable">
    <code>
     Y
    </code>
   </em>
   is functionally
        dependent on
   <em class="replaceable">
    <code>
     X
    </code>
   </em>
   .
  </p>
  <p>
   The examples use the
   <code class="literal">
    world
   </code>
   database, which
        can be downloaded from
   <a class="ulink" href="/doc/index-other.html" target="_top">
    https://dev.mysql.com/doc/index-other.html
   </a>
   . You can find details
        on how to install the database on the same page.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="group-by-functional-dependence.html#functional-dependence-keys" title="Functional Dependencies Derived from Keys">
       Functional Dependencies Derived from Keys
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="group-by-functional-dependence.html#functional-dependence-multiple-column-keys" title="Functional Dependencies Derived from Multiple-Column Keys and from Equalities">
       Functional Dependencies Derived from Multiple-Column Keys and from
Equalities
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="group-by-functional-dependence.html#functional-dependence-special-cases" title="Functional Dependency Special Cases">
       Functional Dependency Special Cases
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="group-by-functional-dependence.html#functional-dependence-views" title="Functional Dependencies and Views">
       Functional Dependencies and Views
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="group-by-functional-dependence.html#functional-dependence-combinations" title="Combinations of Functional Dependencies">
       Combinations of Functional Dependencies
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="functional-dependence-keys">
       </a>
       Functional Dependencies Derived from Keys
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following query selects, for each country, a count of
          spoken languages:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56682869"><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> co<span class="token punctuation">.</span><span class="token keyword">Name</span><span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span>
<span class="token keyword">FROM</span> countrylanguage cl<span class="token punctuation">,</span> country co
<span class="token keyword">WHERE</span> cl<span class="token punctuation">.</span>CountryCode <span class="token operator">=</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    <code class="literal">
     co.Code
    </code>
    is a primary key of
    <code class="literal">
     co
    </code>
    , so all columns of
    <code class="literal">
     co
    </code>
    are functionally dependent on it, as expressed using this
          notation:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa97717171"><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">{co<span class="token punctuation">.</span><span class="token keyword">Code</span>} <span class="token operator">-</span><span class="token operator">&gt;</span> {co<span class="token punctuation">.</span><span class="token operator">*</span>}</code></pre>
   </div>
   <p>
    Thus,
    <code class="literal">
     co.name
    </code>
    is functionally dependent on
    <code class="literal">
     GROUP BY
    </code>
    columns and the query is valid.
   </p>
   <p>
    A
    <code class="literal">
     UNIQUE
    </code>
    index over a
    <code class="literal">
     NOT
          NULL
    </code>
    column could be used instead of a primary key
          and the same functional dependence would apply. (This is not
          true for a
    <code class="literal">
     UNIQUE
    </code>
    index that permits
    <code class="literal">
     NULL
    </code>
    values because it permits multiple
    <code class="literal">
     NULL
    </code>
    values and in that case uniqueness is
          lost.)
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="functional-dependence-multiple-column-keys">
       </a>
       Functional Dependencies Derived from Multiple-Column Keys and from
Equalities
      </h4>
     </div>
    </div>
   </div>
   <p>
    This query selects, for each country, a list of all spoken
          languages and how many people speak them:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24628477"><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> co<span class="token punctuation">.</span><span class="token keyword">Name</span><span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span><span class="token punctuation">,</span>
cl<span class="token punctuation">.</span>Percentage <span class="token operator">*</span> co<span class="token punctuation">.</span>Population <span class="token operator">/</span> <span class="token number">100.0</span> <span class="token keyword">AS</span> SpokenBy
<span class="token keyword">FROM</span> countrylanguage cl<span class="token punctuation">,</span> country co
<span class="token keyword">WHERE</span> cl<span class="token punctuation">.</span>CountryCode <span class="token operator">=</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> cl<span class="token punctuation">.</span>CountryCode<span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The pair (
    <code class="literal">
     cl.CountryCode
    </code>
    ,
    <code class="literal">
     cl.Language
    </code>
    ) is a two-column composite
          primary key of
    <code class="literal">
     cl
    </code>
    , so that column pair
          uniquely determines all columns of
    <code class="literal">
     cl
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa42940828"><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">{cl<span class="token punctuation">.</span>CountryCode<span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span>} <span class="token operator">-</span><span class="token operator">&gt;</span> {cl<span class="token punctuation">.</span><span class="token operator">*</span>}</code></pre>
   </div>
   <p>
    Moreover, because of the equality in the
    <code class="literal">
     WHERE
    </code>
    clause:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa99383589"><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">{cl<span class="token punctuation">.</span>CountryCode} <span class="token operator">-</span><span class="token operator">&gt;</span> {co<span class="token punctuation">.</span><span class="token keyword">Code</span>}</code></pre>
   </div>
   <p>
    And, because
    <code class="literal">
     co.Code
    </code>
    is primary key of
    <code class="literal">
     co
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa81299785"><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">{co<span class="token punctuation">.</span><span class="token keyword">Code</span>} <span class="token operator">-</span><span class="token operator">&gt;</span> {co<span class="token punctuation">.</span><span class="token operator">*</span>}</code></pre>
   </div>
   <p>
    <span class="quote">
     “
     <span class="quote">
      Uniquely determines
     </span>
     ”
    </span>
    relationships are
          transitive, therefore:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa61324045"><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">{cl<span class="token punctuation">.</span>CountryCode<span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span>} <span class="token operator">-</span><span class="token operator">&gt;</span> {cl<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">,</span>co<span class="token punctuation">.</span><span class="token operator">*</span>}</code></pre>
   </div>
   <p>
    As a result, the query is valid.
   </p>
   <p>
    As with the previous example, a
    <code class="literal">
     UNIQUE
    </code>
    key
          over
    <code class="literal">
     NOT NULL
    </code>
    columns could be used instead
          of a primary key.
   </p>
   <p>
    An
    <code class="literal">
     INNER JOIN
    </code>
    condition can be used instead
          of
    <code class="literal">
     WHERE
    </code>
    . The same functional dependencies
          apply:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73833728"><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> co<span class="token punctuation">.</span><span class="token keyword">Name</span><span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span><span class="token punctuation">,</span>
cl<span class="token punctuation">.</span>Percentage <span class="token operator">*</span> co<span class="token punctuation">.</span>Population<span class="token operator">/</span><span class="token number">100.0</span> <span class="token keyword">AS</span> SpokenBy
<span class="token keyword">FROM</span> countrylanguage cl <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> country co
<span class="token keyword">ON</span> cl<span class="token punctuation">.</span>CountryCode <span class="token operator">=</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> cl<span class="token punctuation">.</span>CountryCode<span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="functional-dependence-special-cases">
       </a>
       Functional Dependency Special Cases
      </h4>
     </div>
    </div>
   </div>
   <p>
    Whereas an equality test in a
    <code class="literal">
     WHERE
    </code>
    condition or
    <code class="literal">
     INNER JOIN
    </code>
    condition is
          symmetric, an equality test in an outer join condition is not,
          because tables play different roles.
   </p>
   <p>
    Assume that referential integrity has been accidentally broken
          and there exists a row of
    <code class="literal">
     countrylanguage
    </code>
    without a corresponding row in
    <code class="literal">
     country
    </code>
    .
          Consider the same query as in the previous example, but with a
    <code class="literal">
     LEFT JOIN
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59909186"><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> co<span class="token punctuation">.</span><span class="token keyword">Name</span><span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span><span class="token punctuation">,</span>
cl<span class="token punctuation">.</span>Percentage <span class="token operator">*</span> co<span class="token punctuation">.</span>Population<span class="token operator">/</span><span class="token number">100.0</span> <span class="token keyword">AS</span> SpokenBy
<span class="token keyword">FROM</span> countrylanguage cl <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> country co
<span class="token keyword">ON</span> cl<span class="token punctuation">.</span>CountryCode <span class="token operator">=</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> cl<span class="token punctuation">.</span>CountryCode<span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For a given value of
    <code class="literal">
     cl.CountryCode
    </code>
    , the
          value of
    <code class="literal">
     co.Code
    </code>
    in the join result is
          either found in a matching row (determined by
    <code class="literal">
     cl.CountryCode
    </code>
    ) or is
    <code class="literal">
     NULL
    </code>
    -complemented if there is no match
          (also determined by
    <code class="literal">
     cl.CountryCode
    </code>
    ). In
          each case, this relationship applies:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa77391123"><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">{cl<span class="token punctuation">.</span>CountryCode} <span class="token operator">-</span><span class="token operator">&gt;</span> {co<span class="token punctuation">.</span><span class="token keyword">Code</span>}</code></pre>
   </div>
   <p>
    <code class="literal">
     cl.CountryCode
    </code>
    is itself functionally
          dependent on {
    <code class="literal">
     cl.CountryCode
    </code>
    ,
    <code class="literal">
     cl.Language
    </code>
    } which is a primary key.
   </p>
   <p>
    If in the join result
    <code class="literal">
     co.Code
    </code>
    is
    <code class="literal">
     NULL
    </code>
    -complemented,
    <code class="literal">
     co.Name
    </code>
    is as well. If
    <code class="literal">
     co.Code
    </code>
    is not
    <code class="literal">
     NULL
    </code>
    -complemented, then because
    <code class="literal">
     co.Code
    </code>
    is a primary key, it determines
    <code class="literal">
     co.Name
    </code>
    . Therefore, in all cases:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa83864994"><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">{co<span class="token punctuation">.</span><span class="token keyword">Code</span>} <span class="token operator">-</span><span class="token operator">&gt;</span> {co<span class="token punctuation">.</span><span class="token keyword">Name</span>}</code></pre>
   </div>
   <p>
    Which yields:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47932821"><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">{cl<span class="token punctuation">.</span>CountryCode<span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span>} <span class="token operator">-</span><span class="token operator">&gt;</span> {cl<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">,</span>co<span class="token punctuation">.</span><span class="token operator">*</span>}</code></pre>
   </div>
   <p>
    As a result, the query is valid.
   </p>
   <p>
    However, suppose that the tables are swapped, as in this
          query:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa2232322"><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> co<span class="token punctuation">.</span><span class="token keyword">Name</span><span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span><span class="token punctuation">,</span>
cl<span class="token punctuation">.</span>Percentage <span class="token operator">*</span> co<span class="token punctuation">.</span>Population<span class="token operator">/</span><span class="token number">100.0</span> <span class="token keyword">AS</span> SpokenBy
<span class="token keyword">FROM</span> country co <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> countrylanguage cl
<span class="token keyword">ON</span> cl<span class="token punctuation">.</span>CountryCode <span class="token operator">=</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> cl<span class="token punctuation">.</span>CountryCode<span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Now this relationship does
    <span class="emphasis">
     <em>
      not
     </em>
    </span>
    apply:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38481070"><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">{cl<span class="token punctuation">.</span>CountryCode<span class="token punctuation">,</span> cl<span class="token punctuation">.</span><span class="token keyword">Language</span>} <span class="token operator">-</span><span class="token operator">&gt;</span> {cl<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">,</span>co<span class="token punctuation">.</span><span class="token operator">*</span>}</code></pre>
   </div>
   <p>
    Indeed, all
    <code class="literal">
     NULL
    </code>
    -complemented rows made for
    <code class="literal">
     cl
    </code>
    is put into a single group (they have
          both
    <code class="literal">
     GROUP BY
    </code>
    columns equal to
    <code class="literal">
     NULL
    </code>
    ), and inside this group the value of
    <code class="literal">
     co.Name
    </code>
    can vary. The query is invalid and
          MySQL rejects it.
   </p>
   <p>
    Functional dependence in outer joins is thus linked to whether
          determinant columns belong to the left or right side of the
    <code class="literal">
     LEFT JOIN
    </code>
    . Determination of functional
          dependence becomes more complex if there are nested outer
          joins or the join condition does not consist entirely of
          equality comparisons.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="functional-dependence-views">
       </a>
       Functional Dependencies and Views
      </h4>
     </div>
    </div>
   </div>
   <p>
    Suppose that a view on countries produces their code, their
          name in uppercase, and how many different official languages
          they have:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31449917"><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">VIEW</span> country2 <span class="token keyword">AS</span>
<span class="token keyword">SELECT</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span><span class="token punctuation">,</span> <span class="token function">UPPER</span><span class="token punctuation">(</span>co<span class="token punctuation">.</span><span class="token keyword">Name</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> UpperName<span class="token punctuation">,</span>
<span class="token function">COUNT</span><span class="token punctuation">(</span>cl<span class="token punctuation">.</span><span class="token keyword">Language</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> OfficialLanguages
<span class="token keyword">FROM</span> country <span class="token keyword">AS</span> co <span class="token keyword">JOIN</span> countrylanguage <span class="token keyword">AS</span> cl
<span class="token keyword">ON</span> cl<span class="token punctuation">.</span>CountryCode <span class="token operator">=</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span>
<span class="token keyword">WHERE</span> cl<span class="token punctuation">.</span>isOfficial <span class="token operator">=</span> <span class="token string">'T'</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    This definition is valid because:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa62135800"><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">{co<span class="token punctuation">.</span><span class="token keyword">Code</span>} <span class="token operator">-</span><span class="token operator">&gt;</span> {co<span class="token punctuation">.</span><span class="token operator">*</span>}</code></pre>
   </div>
   <p>
    In the view result, the first selected column is
    <code class="literal">
     co.Code
    </code>
    , which is also the group column and
          thus determines all other selected expressions:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa53970221"><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">{country2<span class="token punctuation">.</span><span class="token keyword">Code</span>} <span class="token operator">-</span><span class="token operator">&gt;</span> {country2<span class="token punctuation">.</span><span class="token operator">*</span>}</code></pre>
   </div>
   <p>
    MySQL understands this and uses this information, as described
          following.
   </p>
   <p>
    This query displays countries, how many different official
          languages they have, and how many cities they have, by joining
          the view with the
    <code class="literal">
     city
    </code>
    table:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74832865"><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> co2<span class="token punctuation">.</span><span class="token keyword">Code</span><span class="token punctuation">,</span> co2<span class="token punctuation">.</span>UpperName<span class="token punctuation">,</span> co2<span class="token punctuation">.</span>OfficialLanguages<span class="token punctuation">,</span>
<span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> Cities
<span class="token keyword">FROM</span> country2 <span class="token keyword">AS</span> co2 <span class="token keyword">JOIN</span> city ci
<span class="token keyword">ON</span> ci<span class="token punctuation">.</span>CountryCode <span class="token operator">=</span> co2<span class="token punctuation">.</span><span class="token keyword">Code</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> co2<span class="token punctuation">.</span><span class="token keyword">Code</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    This query is valid because, as seen previously:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38746040"><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">{co2<span class="token punctuation">.</span><span class="token keyword">Code</span>} <span class="token operator">-</span><span class="token operator">&gt;</span> {co2<span class="token punctuation">.</span><span class="token operator">*</span>}</code></pre>
   </div>
   <p>
    MySQL is able to discover a functional dependency in the
          result of a view and use that to validate a query which uses
          the view. The same would be true if
    <code class="literal">
     country2
    </code>
    were a derived table (or common
          table expression), as in:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29194112"><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> co2<span class="token punctuation">.</span><span class="token keyword">Code</span><span class="token punctuation">,</span> co2<span class="token punctuation">.</span>UpperName<span class="token punctuation">,</span> co2<span class="token punctuation">.</span>OfficialLanguages<span class="token punctuation">,</span>
<span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> Cities
<span class="token keyword">FROM</span>
<span class="token punctuation">(</span>
 <span class="token keyword">SELECT</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span><span class="token punctuation">,</span> <span class="token function">UPPER</span><span class="token punctuation">(</span>co<span class="token punctuation">.</span><span class="token keyword">Name</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> UpperName<span class="token punctuation">,</span>
 <span class="token function">COUNT</span><span class="token punctuation">(</span>cl<span class="token punctuation">.</span><span class="token keyword">Language</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> OfficialLanguages
 <span class="token keyword">FROM</span> country <span class="token keyword">AS</span> co <span class="token keyword">JOIN</span> countrylanguage <span class="token keyword">AS</span> cl
 <span class="token keyword">ON</span> cl<span class="token punctuation">.</span>CountryCode<span class="token operator">=</span>co<span class="token punctuation">.</span><span class="token keyword">Code</span>
 <span class="token keyword">WHERE</span> cl<span class="token punctuation">.</span>isOfficial<span class="token operator">=</span><span class="token string">'T'</span>
 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> co<span class="token punctuation">.</span><span class="token keyword">Code</span>
<span class="token punctuation">)</span> <span class="token keyword">AS</span> co2
<span class="token keyword">JOIN</span> city ci <span class="token keyword">ON</span> ci<span class="token punctuation">.</span>CountryCode <span class="token operator">=</span> co2<span class="token punctuation">.</span><span class="token keyword">Code</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> co2<span class="token punctuation">.</span><span class="token keyword">Code</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="functional-dependence-combinations">
       </a>
       Combinations of Functional Dependencies
      </h4>
     </div>
    </div>
   </div>
   <p>
    MySQL is able to combine all of the preceding types of
          functional dependencies (key based, equality based, view
          based) to validate more complex queries.
   </p>
  </div>
 </div>
 <br/>
</div>
