<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="create-table-select">
      </a>
      15.1.20.4 CREATE TABLE ... SELECT Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045185318000">
  </a>
  <a class="indexterm" name="idm46045185316960">
  </a>
  <p>
   You can create one table from another by adding a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement at the end of
        the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa14286032"><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> <em class="replaceable">new_tbl</em> <span class="token punctuation">[</span><span class="token keyword">AS</span><span class="token punctuation">]</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">orig_tbl</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   MySQL creates new columns for all elements in the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   . For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69317508"><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">CREATE</span> <span class="token keyword">TABLE</span> test <span class="token punctuation">(</span>a <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>        <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>a<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>b<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>        <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB <span class="token keyword">SELECT</span> b<span class="token punctuation">,</span>c <span class="token keyword">FROM</span> test2<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This creates an
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   table with
        three columns,
   <code class="literal">
    a
   </code>
   ,
   <code class="literal">
    b
   </code>
   , and
   <code class="literal">
    c
   </code>
   . The
   <code class="literal">
    ENGINE
   </code>
   option is
        part of the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement, and should not be used following the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   ; this would result in a
        syntax error. The same is true for other
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   options such as
   <code class="literal">
    CHARSET
   </code>
   .
  </p>
  <p>
   Notice that the columns from the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement are appended to
        the right side of the table, not overlapped onto it. Take the
        following example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa54922320"><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> foo<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>
<span class="token output"><span class="token punctuation">|</span> n <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>
<span class="token output"><span class="token punctuation">|</span> 1 <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> bar <span class="token punctuation">(</span>m <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">SELECT</span> n <span class="token keyword">FROM</span> foo<span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.02 sec)</span>
<span class="token output">Records: 1  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> bar<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>
<span class="token output"><span class="token punctuation">|</span> m    <span class="token punctuation">|</span> n <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>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> 1 <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>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   For each row in table
   <code class="literal">
    foo
   </code>
   , a row is inserted
        in
   <code class="literal">
    bar
   </code>
   with the values from
   <code class="literal">
    foo
   </code>
   and default values for the new columns.
  </p>
  <p>
   In a table resulting from
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE ...
        SELECT
    </code>
   </a>
   , columns named only in the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   part come first.
        Columns named in both parts or only in the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   part come after that. The
        data type of
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   columns can
        be overridden by also specifying the column in the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   part.
  </p>
  <p>
   For storage engines that support both atomic DDL and foreign key
        constraints, creation of foreign keys is not permitted in
   <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
    <code class="literal">
     CREATE
        TABLE ... SELECT
    </code>
   </a>
   statements when row-based replication
        is in use. Foreign key constraints can be added later using
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   .
  </p>
  <p>
   You can precede the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   by
   <code class="literal">
    IGNORE
   </code>
   or
   <code class="literal">
    REPLACE
   </code>
   to
        indicate how to handle rows that duplicate unique key values.
        With
   <code class="literal">
    IGNORE
   </code>
   , rows that duplicate an existing
        row on a unique key value are discarded. With
   <code class="literal">
    REPLACE
   </code>
   , new rows replace rows that have the
        same unique key value. If neither
   <code class="literal">
    IGNORE
   </code>
   nor
   <code class="literal">
    REPLACE
   </code>
   is specified, duplicate unique key
        values result in an error. For more information, see
   <a class="xref" href="sql-mode.html#ignore-effect-on-execution" title="The Effect of IGNORE on Statement Execution">
    The Effect of IGNORE on Statement Execution
   </a>
   .
  </p>
  <p>
   You can also use a
   <a class="link" href="values.html" title="15.2.19 VALUES Statement">
    <code class="literal">
     VALUES
    </code>
   </a>
   statement in the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   part of
   <code class="literal">
    CREATE TABLE ... SELECT
   </code>
   ; the
   <code class="literal">
    VALUES
   </code>
   portion of the statement must include
        a table alias using an
   <code class="literal">
    AS
   </code>
   clause. To name the
        columns coming from
   <code class="literal">
    VALUES
   </code>
   , supply column
        aliases with the table alias; otherwise, the default column
        names
   <code class="literal">
    column_0
   </code>
   ,
   <code class="literal">
    column_1
   </code>
   ,
   <code class="literal">
    column_2
   </code>
   , ..., are used.
  </p>
  <p>
   Otherwise, naming of columns in the table thus created follows
        the same rules as described previously in this section.
        Examples:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa38714903"><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">CREATE</span> <span class="token keyword">TABLE</span> tv1
     <span class="token operator">&gt;</span>     <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">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> v<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> tv1<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 class="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> column_0 <span class="token punctuation">|</span> column_1 <span class="token punctuation">|</span> column_2 <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 class="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>        3 <span class="token punctuation">|</span>        5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>        4 <span class="token punctuation">|</span>        6 <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 class="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 prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tv2
     <span class="token operator">&gt;</span>     <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">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> v<span class="token punctuation">(</span>x<span class="token punctuation">,</span>y<span class="token punctuation">,</span>z<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> tv2<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>
<span class="token output"><span class="token punctuation">|</span> x <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>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span> 3 <span class="token punctuation">|</span> 5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span> 4 <span class="token punctuation">|</span> 6 <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tv3 <span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">,</span> b <span class="token datatype">INT</span><span class="token punctuation">,</span> c <span class="token datatype">INT</span><span class="token punctuation">)</span>
     <span class="token operator">&gt;</span>     <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">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> v<span class="token punctuation">(</span>x<span class="token punctuation">,</span>y<span class="token punctuation">,</span>z<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> tv3<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 class="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> c    <span class="token punctuation">|</span>        x <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 class="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 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> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>        5 <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> NULL <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>        4 <span class="token punctuation">|</span>        6 <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 class="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 prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tv4 <span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">,</span> b <span class="token datatype">INT</span><span class="token punctuation">,</span> c <span class="token datatype">INT</span><span class="token punctuation">)</span>
     <span class="token operator">&gt;</span>     <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">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> v<span class="token punctuation">(</span>x<span class="token punctuation">,</span>y<span class="token punctuation">,</span>z<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> tv4<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 class="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> x <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 class="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> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> 1 <span class="token punctuation">|</span> 3 <span class="token punctuation">|</span> 5 <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> NULL <span class="token punctuation">|</span> 2 <span class="token punctuation">|</span> 4 <span class="token punctuation">|</span> 6 <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 class="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 prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tv5 <span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">,</span> b <span class="token datatype">INT</span><span class="token punctuation">,</span> c <span class="token datatype">INT</span><span class="token punctuation">)</span>
     <span class="token operator">&gt;</span>     <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">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> v<span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> tv5<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>    3 <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>    6 <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>
   When selecting all columns and using the default column names,
        you can omit
   <code class="literal">
    SELECT *
   </code>
   , so the statement just
        used to create table
   <code class="literal">
    tv1
   </code>
   can also be written
        as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74354426"><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">CREATE</span> <span class="token keyword">TABLE</span> tv1 <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> tv1<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 class="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> column_0 <span class="token punctuation">|</span> column_1 <span class="token punctuation">|</span> column_2 <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 class="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>        3 <span class="token punctuation">|</span>        5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>        4 <span class="token punctuation">|</span>        6 <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 class="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>
   When using
   <a class="link" href="values.html" title="15.2.19 VALUES Statement">
    <code class="literal">
     VALUES
    </code>
   </a>
   as the source
        of the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   , all columns are
        always selected into the new table, and individual columns
        cannot be selected as they can be when selecting from a named
        table; each of the following statements produces an error
        (
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_operand_columns" target="_top">
    <code class="literal">
     ER_OPERAND_COLUMNS
    </code>
   </a>
   ):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa38557289"><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> tvx
    <span class="token keyword">SELECT</span> <span class="token punctuation">(</span>x<span class="token punctuation">,</span>z<span class="token punctuation">)</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> v<span class="token punctuation">(</span>x<span class="token punctuation">,</span>y<span class="token punctuation">,</span>z<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tvx <span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">,</span> c <span class="token datatype">INT</span><span class="token punctuation">)</span>
    <span class="token keyword">SELECT</span> <span class="token punctuation">(</span>x<span class="token punctuation">,</span>z<span class="token punctuation">)</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> v<span class="token punctuation">(</span>x<span class="token punctuation">,</span>y<span class="token punctuation">,</span>z<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Similarly, you can use a
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   statement in place of the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   .
        This follows the same rules as with
   <a class="link" href="values.html" title="15.2.19 VALUES Statement">
    <code class="literal">
     VALUES
    </code>
   </a>
   ; all columns of the source
        table and their names in the source table are always inserted
        into the new table. Examples:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15549529"><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">TABLE</span> t1<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>
<span class="token output"><span class="token punctuation">|</span> a  <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>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span>  2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span>  7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 10 <span class="token punctuation">|</span> <span class="token punctuation">-</span>4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 14 <span class="token punctuation">|</span>  6 <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tt1 <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> tt1<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>
<span class="token output"><span class="token punctuation">|</span> a  <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>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span>  2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span>  7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 10 <span class="token punctuation">|</span> <span class="token punctuation">-</span>4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 14 <span class="token punctuation">|</span>  6 <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tt2 <span class="token punctuation">(</span>x <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> tt2<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>
<span class="token output"><span class="token punctuation">|</span> x    <span class="token punctuation">|</span> a  <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>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>  1 <span class="token punctuation">|</span>  2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>  6 <span class="token punctuation">|</span>  7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> 10 <span class="token punctuation">|</span> <span class="token punctuation">-</span>4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> 14 <span class="token punctuation">|</span>  6 <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></code></pre>
  </div>
  <p>
   Because the ordering of the rows in the underlying
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements cannot always
        be determined,
   <code class="literal">
    CREATE TABLE ... IGNORE SELECT
   </code>
   and
   <code class="literal">
    CREATE TABLE ... REPLACE SELECT
   </code>
   statements are flagged as unsafe for statement-based
        replication. Such statements produce a warning in the error log
        when using statement-based mode and are written to the binary
        log using the row-based format when using
   <code class="literal">
    MIXED
   </code>
   mode. See also
   <a class="xref" href="replication-sbr-rbr.html" title="19.2.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication">
    Section 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based
        Replication”
   </a>
   .
  </p>
  <p>
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE ...
        SELECT
    </code>
   </a>
   does not automatically create any indexes for
        you. This is done intentionally to make the statement as
        flexible as possible. If you want to have indexes in the created
        table, you should specify these before the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa74516547"><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">CREATE</span> <span class="token keyword">TABLE</span> bar <span class="token punctuation">(</span><span class="token keyword">UNIQUE</span> <span class="token punctuation">(</span>n<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">SELECT</span> n <span class="token keyword">FROM</span> foo<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For
   <code class="literal">
    CREATE TABLE ... SELECT
   </code>
   , the destination
        table does not preserve information about whether columns in the
        selected-from table are generated columns. The
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   part of the statement
        cannot assign values to generated columns in the destination
        table.
  </p>
  <p>
   For
   <code class="literal">
    CREATE TABLE ... SELECT
   </code>
   , the destination
        table does preserve expression default values from the original
        table.
  </p>
  <p>
   Some conversion of data types might occur. For example, the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   attribute is not preserved,
        and
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   columns can become
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     CHAR
    </code>
   </a>
   columns. Retrained
        attributes are
   <code class="literal">
    NULL
   </code>
   (or
   <code class="literal">
    NOT
        NULL
   </code>
   ) and, for those columns that have them,
   <code class="literal">
    CHARACTER SET
   </code>
   ,
   <code class="literal">
    COLLATION
   </code>
   ,
   <code class="literal">
    COMMENT
   </code>
   , and the
   <code class="literal">
    DEFAULT
   </code>
   clause.
  </p>
  <p>
   When creating a table with
   <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
    <code class="literal">
     CREATE
        TABLE ... SELECT
    </code>
   </a>
   , make sure to alias any function
        calls or expressions in the query. If you do not, the
   <code class="literal">
    CREATE
   </code>
   statement might fail or result in
        undesirable column names.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60624195"><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> artists_and_works
  <span class="token keyword">SELECT</span> artist<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 keyword">work</span><span class="token punctuation">.</span>artist_id<span class="token punctuation">)</span> <span class="token keyword">AS</span> number_of_works
  <span class="token keyword">FROM</span> artist <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token keyword">work</span> <span class="token keyword">ON</span> artist<span class="token punctuation">.</span>id <span class="token operator">=</span> <span class="token keyword">work</span><span class="token punctuation">.</span>artist_id
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> artist<span class="token punctuation">.</span>id<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can also explicitly specify the data type for a column in
        the created table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38030509"><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> foo <span class="token punctuation">(</span>a <span class="token datatype">TINYINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span> <span class="token keyword">SELECT</span> b<span class="token operator">+</span><span class="token number">1</span> <span class="token keyword">AS</span> a <span class="token keyword">FROM</span> bar<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
        ... SELECT
    </code>
   </a>
   , if
   <code class="literal">
    IF NOT EXISTS
   </code>
   is
        given and the target table exists, nothing is inserted into the
        destination table, and the statement is not logged.
  </p>
  <p>
   To ensure that the binary log can be used to re-create the
        original tables, MySQL does not permit concurrent inserts during
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE ...
        SELECT
    </code>
   </a>
   . For more information, see
   <a class="xref" href="atomic-ddl.html" title="15.1.1 Atomic Data Definition Statement Support">
    Section 15.1.1, “Atomic Data Definition Statement Support”
   </a>
   .
  </p>
  <p>
   You cannot use
   <code class="literal">
    FOR UPDATE
   </code>
   as part of the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   in a statement such as
   <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
    <code class="literal">
     CREATE
        TABLE
     <em class="replaceable">
      <code>
       new_table
      </code>
     </em>
     SELECT ... FROM
     <em class="replaceable">
      <code>
       old_table
      </code>
     </em>
     ...
    </code>
   </a>
   . If you
        attempt to do so, the statement fails.
  </p>
  <p>
   <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
    <code class="literal">
     CREATE
        TABLE ... SELECT
    </code>
   </a>
   operations apply
   <code class="literal">
    ENGINE_ATTRIBUTE
   </code>
   and
   <code class="literal">
    SECONDARY_ENGINE_ATTRIBUTE
   </code>
   values to columns
        only. Table and index
   <code class="literal">
    ENGINE_ATTRIBUTE
   </code>
   and
   <code class="literal">
    SECONDARY_ENGINE_ATTRIBUTE
   </code>
   values are not
        applied to the new table unless specified explicitly.
  </p>
 </div>
 <br/>
</div>
