Create Table Through JOIN SQL

Question

I need to create a new table in my database through a left join statement of two tables from different schemas. Here is my code below:

CREATE TABLE NEW_TABLE
  FROM SCHEMA_1.TABLE_A X
  LEFT JOIN SCHEMA_2.TABLE_B Y
     ON X.NAME = Y.NAME 
        AND X.NUMBER = Y.NUMBER

I'm getting a SQL error:

ORA-00922: missing or invalid option
00922. 00000 - "missing or invalid option"

Answer 1

You are missing the as select clause:

CREATE TABLE NEW_TABLE
AS ( -- This was missing
    SELECT    * -- So was this
    FROM      SCHEMA_1.TABLE_A X
    LEFT JOIN SCHEMA_2.TABLE_B Y ON X.NAME = Y.NAME AND
                                    X.NUMBER = Y.NUMBER
   )