Using "AS" in MySQL - not as aliases

Question

Hi, I was kinda surprised when I saw following:

CREATE TEMPORARY TABLE X (ID int) AS SELECT NumColumn FROM Table

I have tried to google it but only found using this as alieases. What this use actually is? I feel bit confused since I was stupidly creating temporary table and then fill it by using of insert..

Thank you

Answer 1

Its how you create a temporary table and populate it with the results of a select query.

You can see it in the documentation at the very bottom of the CREATE TABLE specification

select_statement:
[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)

Answer 2

This will create a temporary table for you but just remember that this will not bring along any indexes you had on the original table. For that reason, sometimes it may be better create a complete copy of the table definition using

create table x like y; 

Since this only creates an EMPTY table, you need to also run

insert into x (col1) select col1 from y;